IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetch record which is added today & is not available alr


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 6966
Location: porcelain throne

PostPosted: Tue Dec 04, 2007 10:51 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Dec 04, 2007 11:08 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
Search our Forums:

Back to Top