Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 6966
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: 10211
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us