|
View previous topic :: View next topic
|
| Author |
Message |
harisukumaran
New User
Joined: 14 Jun 2005
Posts: 75
|
|
|
|
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 |
|
 |
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
kgumraj
Active User
Joined: 01 May 2006
Posts: 151
Location: Hyderabad
|
|
|
|
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 |
|
|
|
|
