Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Fetch record which is added today & is not available alr

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
karthikbabudh

New User


Joined: 08 Sep 2003
Posts: 32
Location: Toronto <-> Bangalore

PostPosted: Tue Dec 04, 2007 10:20 pm    Post subject: Fetch record which is added today & is not available alr
Reply with quote

Hi,
I have table like this.

Field1 Date Added
=============
REP01 30-11-2007
REP02 01-12-2007
REP03 02-12-2007
REP04 03-12-2007
REP01 04-12-2007
REP05 04-12-2007

I want to fetch only the records which are added today and which already does not exists. So the output I' am expecting from above table is
"REP05 04-12-2007". Is it possible using simple SQL?

Thanks,
Karthik.
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Dec 04, 2007 10:27 pm    Post subject: Re: Fetch record which is added today & is not available
Reply with quote

karthikbabudh wrote:

which already does not exists


You need to explain that a little better.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Dec 04, 2007 10:51 pm    Post subject:
Reply with quote

and why do you NOT want REP01 04-12-2007?
Back to top
View user's profile Send private message
karthikbabudh

New User


Joined: 08 Sep 2003
Posts: 32
Location: Toronto <-> Bangalore

PostPosted: Tue Dec 04, 2007 10:58 pm    Post subject: Reply to: Fetch record which is added today & is not ava
Reply with quote

Dick,

I want all the "Field1" values which are added today but is not available for any of the existing dates. In my table, 2 records are added today.

REP01 04-12-2007
REP05 04-12-2007

Out of the above 2 records, REP01 is already available for the date "30-11-2007" (1st record in table). So I want only REP05. Hope I answered your question.

Thanks,
Karthik.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Tue Dec 04, 2007 11:08 pm    Post subject: Reply to: Fetch record which is added today & is not ava
Reply with quote

why not use a query in the style of

Code:
select what_you_need from Your_table
were date = todays_date and
     date not in ( select date from Your_table were date less_then todays_date )
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Dec 04, 2007 11:43 pm    Post subject:
Reply with quote

Code:
SELECT FIELD1 FROM TABLE A
WHERE DATE_ADDED = CURRENT DATE
AND NOT EXISTS (SELECT 1 FROM TABLE B
                            WHERE A.FIELD1 = B.FIELD1
                               AND  B.DATE_ADDED < A.DATE_ADDED)
Back to top
View user's profile Send private message
karthikbabudh

New User


Joined: 08 Sep 2003
Posts: 32
Location: Toronto <-> Bangalore

PostPosted: Wed Dec 05, 2007 12:56 am    Post subject: Reply to: Fetch record which is added today & is not ava
Reply with quote

I got it. Thanks Craq and Enrico

Karthik.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us