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
 

 

To select a value if the date field in a group is not NULL

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

New User


Joined: 14 Aug 2007
Posts: 14
Location: Chennai

PostPosted: Wed Jun 12, 2013 9:15 am    Post subject: To select a value if the date field in a group is not NULL
Reply with quote

Code:

TABLE1
SNO
1
2


TABLE2:
SNO DATE_FIELD_01  DATE_FIELD_02
1   2013-01-01   2013-01-01
1   2013-01-01   2013-01-01
1   2013-01-01   2013-01-01
1   2013-01-01   NULL
2   2013-01-01   2013-01-01
2   2013-01-01   2013-01-01   
2   2013-01-01   2013-01-01   
2   2013-01-01   2013-01-01


I need a query which satisfies the following condition.

If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 value in table2 is NOT NULL then the
query should pick that SNO alone

In the above example the query should return the SNO as 2

Your help is appreciated.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jun 12, 2013 11:13 am    Post subject: Reply to: TO SELECT A VALUE IF THE DATE FIELD IN A GROUP IS
Reply with quote

Do you mean:

If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 table2 is NULL then the query should pick that SNO alone.

Otherwise, how would 2 be selected?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed Jun 12, 2013 11:15 am    Post subject:
Reply with quote

sthirumalai,

How about writing a correlated sub query which selects each SNO from TABLE1 with a NOT EXISTS check in TABLE2 for DATE_FIELD_02 IS NULL for the selected SNO.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed Jun 12, 2013 11:17 am    Post subject:
Reply with quote

Thats a double -ve Bill. icon_smile.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jun 12, 2013 11:46 am    Post subject: Reply to: TO SELECT A VALUE IF THE DATE FIELD IN A GROUP IS
Reply with quote

Thanks Arun. It's funny, I read it several times earlier, and couldn't get it to make any sense to me. Now, reading it again, I can't see what I found so unclear. Perhaps I was still asleep :-)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed Jun 12, 2013 12:06 pm    Post subject:
Reply with quote

Hi Bill,

I meant you were right, now I see that I made the same mistake as the OPs, edited my NOT NULL to NULL now. icon_biggrin.gif

Have a good one!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jun 12, 2013 12:47 pm    Post subject: Reply to: To select a value if the date field in a group is
Reply with quote

I must still be asleep. I've changed the subject from all CAPS, just for something simple to do... topics then fit better on the page.

If TS would confirm, I'd change the original and have done with it...
Back to top
View user's profile Send private message
sthirumalai

New User


Joined: 14 Aug 2007
Posts: 14
Location: Chennai

PostPosted: Wed Jun 12, 2013 8:28 pm    Post subject: Reply to: To select a value if the date field in a group is
Reply with quote

Bill, You are correct

If there is a matching SNO from TABLE1 in TABLE 2 and no DATE_FIELD_02 table2 is NULL then the query should pick that SNO alone.

Otherwise, how would 2 be selected?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 12, 2013 8:32 pm    Post subject:
Reply with quote

Try this

Code:
SELECT A.SNO
FROM TABLE1 A,TABLE2 B
WHERE A.SNO=B.SNO
AND   B.DATE_FIELD_02 IS NULL;
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Wed Jun 12, 2013 9:43 pm    Post subject:
Reply with quote

Pandora-Box wrote:
Try this

Code:
SELECT A.SNO
FROM TABLE1 A,TABLE2 B
WHERE A.SNO=B.SNO
AND   B.DATE_FIELD_02 IS NULL;
Pandora-Box,

Are you sure this will give '2'? Your sql will give ONLY '1'.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 12, 2013 10:21 pm    Post subject:
Reply with quote

Arun,

Ooopps If Op needs 2 This should be a subquery within like below

Code:
Select Sno from table1
where not exists (SELECT A.SNO FROM TABLE1 A,TABLE2 B WHERE A.SNO=B.SNO AND B.DATE_FIELD_02 IS NULL)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2158
Location: @my desk

PostPosted: Thu Jun 13, 2013 12:15 am    Post subject:
Reply with quote

Hi Pandora,

Still there is no need to JOIN TABLE1 and TABLE2 in the sub select. As told earlier, a correlated subquery might be better than joining the table again.

Something like this,

Code:
SELECT SNO FROM TABLE1 A
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 B
                  WHERE A.SNO=B.SNO
                  AND   B.DATE_FIELD_02 IS NULL)
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 13, 2013 8:03 am    Post subject:
Reply with quote

;-) Agreed
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 Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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