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
 
Inline views from lists which have a certain combination

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

New User


Joined: 14 Jun 2005
Posts: 33

PostPosted: Thu Nov 23, 2006 2:10 am    Post subject: Inline views from lists which have a certain combination
Reply with quote

Hello Everybody,
Is it possible to obtain rows from a table which have a certain combination
of values in 2 columns.
I know I could do
Code:
---------------------------------------------------
select * from table1
where
(column1 = 1111 and column2 = 2222)
or
(column1 = 3333 and column2 = 4444)
or
...
...
...

----------------or------------------------------
select * from table1
where digits(column1) concat digits(column2)
in
('11112222',
 '33334444',
 ...
 ...
 ...
)

But hey! there must be a cleaner way to do this! It is not a rare requirement

I mean something like this(although it is not syntactically correct):

Code:
select * from table1
where
column1,column2
in
((1111,2222),
 (3333,4444),
...
...
...

)

After all DB2 V8 supports queries like

Code:
select * from table1
where (column1,column2)
in
(
select column1,column2
from table1
group by
column1,column2
having count(distinct column3) > 1
)
Back to top
View user's profile Send private message

priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Thu Nov 23, 2006 9:15 am    Post subject: Re: Inline views from lists???
Reply with quote

Hi Hari,

We can try something like this...
Code:
SELECT *
FROM CREATORID.TABLE-NAME
WHERE CONCAT(COLUMN1,COLUMN2) IN
(
'11112222',
'33334444',
);

But one thing needs to be noticed here that, values given in "IN" clause should exactly match as a combination of COLUMN1 & COLUMN2.
Lets say if values are:-
Column1 C(5) = '1111$' ($= Space)
Column2 C(5) = '2222$' ($= Space)
then value set in "IN" clause should have a value like "1111$2222$" ($= Space)

HTH.
Back to top
View user's profile Send private message
kgumraj

Active User


Joined: 01 May 2006
Posts: 151
Location: Hyderabad

PostPosted: Thu Nov 23, 2006 2:16 pm    Post subject: Re: Inline views from lists???
Reply with quote

One more thing to add to priyesh is you can give "Like" to get the data when you are not sure about the exact value.

Ex:
select * from table1
where col1 like 'filed%';
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 in-stream data set (inline data set) ... Andi1982 JCL & VSAM 4 Thu Jul 07, 2016 2:59 pm
No new posts Getting error - COBOL INLINE ERROR DI... satish.sanagavarapu CLIST & REXX 14 Tue Sep 23, 2014 7:36 pm
No new posts Combination of qualified and unqualif... Appu IMS DB/DC 2 Tue Nov 12, 2013 5:21 pm
No new posts Inline Image copy with Reorg gylbharat DB2 4 Fri Aug 09, 2013 12:31 am
No new posts Combination String search Keanehelp All Other Mainframe Topics 4 Fri Jul 05, 2013 2:48 pm

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