Using a hard coded value and sub query in an IN clause

Question

I have this query:

SELECT *
FROM Parent.Table
WHERE Ref_Num IN ('4205' or (SELECT Ref_Num FROM Child.Table WHERE Ref_Num ='234'))
AND Asset_Type IN ('PUMPS','COLL')

This query doesn't work. The subquery returns 53 rows, instead of typing out all the children ref numbers is there a way to put them in the query along with the parent number of '4205'.

Thanks.

Try `UNION` like `SELECT 4205 UNION ALL /*Your Query*/`. – user2989408 Feb 24 '14 at 17:42 — user2989408, Feb 24 '14 at 17:42

Bob Jarvis - Слава Україні · Accepted Answer · 2014-02-24T17:50:00.420

1

Perhaps you could try something like

SELECT *
  FROM Parent.Table
  WHERE Ref_Num In (SELECT Ref_Num
                      FROM Child.Table
                      WHERE Ref_Num ='234'
                    UNION
                      SELECT '4205' As Ref_Num
                        FROM SYSIBM.SYSDUMMY1)
    AND Asset_Type IN ('PUMPS','COLL')

Note that SYSDUMMY1 is an EBCDIC table. If you need ASCII, use SYSDUMMYA. If you need Unicode, use SYSDUMMYU.

Share and enjoy.

edited Feb 24 '14 at 17:50

answered Feb 24 '14 at 17:43

Bob Jarvis - Слава Україні

48,992
9
77
110

Given that he's not using the value contained in the dummy table, does it matter what the encoding of the field is? Or does the optimizer auto-convert if you reference the table? – Clockwork-Muse Feb 25 '14 at 09:51

score 0 · Answer 2 · answered Feb 24 '14 at 17:42

0

Yes, use a union query

 select 4205 ref_num
 from some_small_table
 union
 select ref_num
 from child.table
 etc

answered Feb 24 '14 at 17:42

Dan Bracuk

20,699
4
26
43

This doesn't work as a filter to only return the records with the ref numbers I want. It just adds to the returned rows. – Sewder Feb 24 '14 at 17:55

Using a hard coded value and sub query in an IN clause

2 Answers2