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
 

 

Need Help with where Clause

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

New User


Joined: 19 Apr 2005
Posts: 7

PostPosted: Tue Dec 23, 2008 1:22 am    Post subject: Need Help with where Clause
Reply with quote

I have a column which can have values '001', '002' , '003' etc and I need to select only those records which ONLY have a value '001' and should not contain records with '002' or '003'..

For e.g.

Member 1 '001'
Member 1 '002'
Member 2 '001'
Member 3 '002'

From this I would need only Member 2 to be selected.

Can you help?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 23, 2008 2:55 am    Post subject:
Reply with quote

Hello,

Please review your request. . . .

Quote:
select only those records which ONLY have a value '001'

"Member 1 '001' " has only '001' icon_confused.gif
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Tue Dec 23, 2008 10:02 am    Post subject:
Reply with quote

I think chiraag is looking for SQL that will return all rows that have MEMBER where '001' is the value of a column and there are no other MEMBER rows that have a column value of anything other than '001'. I don't know if I explained that well or not. icon_smile.gif
Back to top
View user's profile Send private message
chiraag

New User


Joined: 19 Apr 2005
Posts: 7

PostPosted: Wed Dec 24, 2008 1:57 am    Post subject:
Reply with quote

Terry you correctly stated what I needed. Can someone help?
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Wed Dec 24, 2008 3:30 am    Post subject:
Reply with quote

Sorry still not clear for me.
Please describe ( for me ) concerned columns : name, type, possible values,..
Back to top
View user's profile Send private message
chiraag

New User


Joined: 19 Apr 2005
Posts: 7

PostPosted: Wed Dec 24, 2008 3:48 am    Post subject:
Reply with quote

Let me try again -

Lets say my table has

Member No Type
-------------- -------
Member1 001
Member1 002
Member2 001
Member3 001
Member3 003
Member4 001

I want a query which will give me the output

Member2
Member4

as they are the only ones which have Type values '001' and nothing else.
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Wed Dec 24, 2008 4:30 am    Post subject:
Reply with quote

I am not a great DB2 expert, maybe this one

Code:

Select Member from your.table
where Type = '001'
and   Member not in
( select Member from your.table
   where Type <> '001' )
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Dec 24, 2008 10:50 am    Post subject:
Reply with quote

PeD wrote:
I am not a great DB2 expert, maybe this one

Code:

Select Member from your.table
where Type = '001'
and   Member not in
( select Member from your.table
   where Type <> '001' )


I believe this sql consider about only 001 and not the generic one.
I guess he needs the output which is having only one entry in the table.
Please correct me if my understanding is wrong!!!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 24, 2008 10:59 am    Post subject:
Reply with quote

Hello,

Quote:
Please correct me if my understanding is wrong!!!
I believe this is true.

Only the rows with '001' are candidates and then only when there are no other values for that same member.

Quote:
as they are the only ones which have Type values '001' and nothing else.
Please review the complete post this quote was taken from.
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Dec 24, 2008 11:11 am    Post subject:
Reply with quote

Yeah, you are right. Sorry about this.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 24, 2008 12:44 pm    Post subject: Reply to: Need Help with where Clause
Reply with quote

Hi Rajesh,

Not to worry - we all misread or misunderstand a post now and again icon_wink.gif

d
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Wed Dec 24, 2008 3:53 pm    Post subject:
Reply with quote

chiraag,

How about trying something like this?

Code:
   SELECT MEMBER,MAX(TYPE)   
     FROM YOUR.TABLE         
 GROUP BY MEMBER             
   HAVING COUNT(*)  = 1       
      AND MAX(TYPE) = '001'; 


Quote:
we all misread or misunderstand a post now and again
But we compensate for that by providing multiple solutions icon_wink.gif
Back to top
View user's profile Send private message
sriraj1122

New User


Joined: 18 Dec 2008
Posts: 15
Location: Hyderabad

PostPosted: Tue Dec 30, 2008 3:08 pm    Post subject: Try this out
Reply with quote

SELECT * FROM TEMP
WHERE NUM = '001'
AND MEMBER NOT IN
(SELECT MEMBER FROM TEMPSK WHERE NUM <> '001')

Just check this qrery and let me know whether you got the desrired result or not chiraag. :-)


Regards,
Sriraj K
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Tue Dec 30, 2008 3:13 pm    Post subject:
Reply with quote

Why repeat an already given solution?

To add one post?

Because noone reads completely the post?

Don't know! icon_question.gif icon_exclaim.gif icon_question.gif
Back to top
View user's profile Send private message
rajeshwarch

New User


Joined: 24 Mar 2008
Posts: 29
Location: mumbai

PostPosted: Tue Dec 30, 2008 5:34 pm    Post subject:
Reply with quote

Hi friend,
try this one may be this will fulfil your requirement

SELECT * FROM YOUR_TABLE
WHERE TYPE NOT IN ('002', '003')
AND MEMBER_NO IN ( SELECT MEMBER_NO FROM YOUR_TABLE
WHERE TYPE = '001')

As per my view the inner query give you the MEMBER_NOs which have TYPE '001' then outer query verifies whether that MEMBER_NOs have other TYPEs('002' or '003').
The result table will have the MEMBER_NOs which have TYPE '001' only.
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Tue Dec 30, 2008 6:40 pm    Post subject:
Reply with quote

An what about if TYPE=004 exists ??
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 Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm
No new posts 3-D Table with nested DEPENDING ON Cl... VivekKhanna COBOL Programming 5 Sat Aug 08, 2015 11:18 pm
No new posts when=group clause: question about the... tuxama DFSORT/ICETOOL 2 Thu Mar 26, 2015 12:51 pm
No new posts pic clause on group variable error CuriousMainframer COBOL Programming 6 Mon Jul 14, 2014 6:33 pm


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