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: 189
Location: INDIA

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: 1474
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: 189
Location: INDIA

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: 1753
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: 189
Location: INDIA

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: 1280
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 Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts Replacing same string with different ... vickey_dw DFSORT/ICETOOL 6 Wed Feb 22, 2017 10:44 pm
No new posts Formating the Input file sreekusr DFSORT/ICETOOL 3 Fri Feb 17, 2017 10:40 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm


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