CODEWITHSUNDEEP
sqldb2ibm-midrangedb2-400
user5085719
user5085719

Reputation:

DB2 SQL - Possible to query a list of values without using "or" for each row of values?

I have a list of values and I am wondering if it's possible to query by said list, rather than having to do an "or" for each row of values or add them into a table and query the table (as I'm about to do)

Say, for example, my list is;

010, 46793, '329', '10'
011, 46798, '322', '12'
012, 33333, '111', '14'

I'd like to query like this; 

SELECT VALUE1, VALUE2, VALUE3
  FROM MYTABLE
 WHERE (VALUEW VALUEX, VALUEY, VALUEZ) in(
        (010, 46793, '329', '10'),
        (011, 46798, '322', '12'),
        (012, 33333, '111', '14'))

(This fails on syntax)

Rather than having to do;

SELECT VALUE1, VALUE2, VALUE3
  FROM MYTABLE
 WHERE (VALUEW VALUEX, VALUEY, VALUEZ) = (010, 46793, '329', '10')
    OR (VALUEW VALUEX, VALUEY, VALUEZ) = (011, 46798, '322', '12')
    OR (VALUEW VALUEX, VALUEY, VALUEZ) = (012, 33333, '111', '14')

Please note that something like;

SELECT VALUE1, VALUE2, VALUE3
  FROM MYTABLE
 WHERE VALUEW IN(010, 011, 012)
   AND VALUEX IN(46793, 46798, 33333) 
   AND VALUEY IN('329', '322', '111')
   AND VALUEZ IN('10', '12', '14')

Will not work in this scenario.

For example, if the below value existed in "MYTABLE";

011, 33333, '329', '10'

The aforementioned SQL would retrieve it, yet it's not on my list.

Upvotes: 3

Views: 9509

Answers (3)

Tmdean
Tmdean

Reputation: 9299

I've got an alternate solution for non-LUW DB2 that makes use of the XMLTABLE function. This allows many more rows than the SYSDUMMY1 UNION ALL approach.

SELECT TEST_NM, ITM_CD
FROM XMLTABLE('$doc/xml/node'
    PASSING XMLPARSE('<xml>
        <node test-nm="ABC 123" itm-cd="215" />
        <node test-nm="DEF 345" itm-cd="151" />
        <node test-nm="GHI 678" itm-cd="412" />
        <node test-nm="JKL 901" itm-cd="410" />
        <node test-nm="MNO 234" itm-cd="875" />
        <node test-nm="PQR 567" itm-cd="249" />
        <node test-nm="STU 890" itm-cd="817" />
    </xml>') AS "doc"
    COLUMNS TEST_NM VARCHAR(30) PATH '@test-nm',
        ITM_CD INTEGER PATH '@itm-cd') AS Q;

Upvotes: 0

mao
mao

Reputation: 12267

Try the syntax below (it is valid on Db2 LUW v11) not sure of Db2 for i:

SELECT VALUE1, VALUE2, VALUE3
FROM  MYTABLE
where (VALUEW, VALUEX, VALUEY, VALUEZ) in ( values
        (010, 46793, '329', '10'),
        (011, 46798, '322', '12'),
        (012, 33333, '111', '14')
        );

Upvotes: 5

Stavr00
Stavr00

Reputation: 3314

Altenate solution for non-LUW DB2: 

WITH mylist(w,x,y,z) AS (
SELECT  010, 46793, '329', '10' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT  011, 46798, '322', '12' FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT  012, 33333, '111', '14' FROM SYSIBM.SYSDUMMY1 
)
SELECT VALUE1, VALUE2, VALUE3
FROM MYTABLE
INNER JOIN mylist
ON (VALUEW, VALUEX, VALUEY, VALUEZ)=(w,x,y,z)

Upvotes: 0

Related Questions