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
 

 

SELECT the key whose column has all the input list of values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 200
Location: USA

PostPosted: Mon Jul 30, 2012 7:05 pm    Post subject: SELECT the key whose column has all the input list of values
Reply with quote

Hi,

My Table has the values below.

Code:
Key         Origin
-----        -------
1              CS
1              CO
2              AB
2              BC
2              CO
3              CO
3              AB
4              CO
4              CS
My requirement is to get the keys who Origin contains both CO,AB. So for this case I am expecting keys 2,3 to be returned to the output.

I tried to search forum for different usages of predicates ALL and selecting DISTINCT. But I couldn't find much.

Can some one help please?

Regards
Amar

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

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1491
Location: Chennai

PostPosted: Mon Jul 30, 2012 7:38 pm    Post subject:
Reply with quote

Hello,
Will this work out?
Code:

SELECT DISTINCT KEY FROM TABLE1 WHERE ORIGIN = 'CO'   
INTERSECT                                             
SELECT DISTINCT KEY FROM TABLE1 WHERE ORIGIN = 'AB'; 
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 200
Location: USA

PostPosted: Mon Jul 30, 2012 8:44 pm    Post subject:
Reply with quote

Hi Vasanth,

Thank you. It is working. But if we have more values in the input, we got to keep adding the queries. Would there be any alternate way possible to provide the list of values.

Regards
Amar
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1755
Location: Bloomington, IL

PostPosted: Mon Jul 30, 2012 8:48 pm    Post subject:
Reply with quote

This appears to be another case of "stone soup" requirements. icon_axe.gif
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: Mon Jul 30, 2012 9:31 pm    Post subject:
Reply with quote

Hello,

Quote:
But if we have more values in the input, we got to keep adding the queries. Would there be any alternate way possible to provide the list of values
You could always simply write a program that would input the values at run-time and go on from there. . .
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 200
Location: USA

PostPosted: Mon Jul 30, 2012 10:15 pm    Post subject:
Reply with quote

Hi,

Programming is also an option. But thinking to keep it as last option, if I can't get any alternate solution at all for this requirement.

Thanks for suggestion.

Regards
Amar
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jul 31, 2012 1:29 pm    Post subject:
Reply with quote

If (Key,Origin) is a unique key, you could code it like this. But I doubt it will be faster :
Code:
select key from table1 where origin in ('CO','AB')
group by key
having count(*) = 2
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts member list compare jzhardy TSO/ISPF 6 Fri May 12, 2017 3:18 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am


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