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
 
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: 7315

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: 2285
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: 2285
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: 7315

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: 2285
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: 7315

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: 1542
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: 2285
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: 1542
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: 2285
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: 1542
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 Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 pm
This topic is locked: you cannot edit posts or make replies. Can a alphanumeric data be moved to a... nico_neoz COBOL Programming 10 Mon Oct 30, 2017 8:28 am
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm

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