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: 6970
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: 10457
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 VSAM - Accessing beyond record size Benchwarmer JCL & VSAM 6 Wed Apr 11, 2018 9:20 pm
No new posts Logic to skip Maximum time-stamp and ... vickey_dw DB2 2 Thu Feb 01, 2018 11:15 pm
No new posts Copying only first and last word from... chandan.inst SYNCSORT 4 Wed Jan 31, 2018 4:19 pm
No new posts Query to skip Max time-stamp and fetc... vickey_dw DB2 8 Sun Jan 28, 2018 1:09 am
No new posts How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm

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