Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    12

    Unanswered: temporary table within query

    Consider table1 with "col1" as primary key.

    SELECT * from table1 WHERE col1 IN (1,12,234,42,2)

    Say that for value 12 in the above query, there is no matching row, only 4 rows are returned. But for my purpose, I need to get all 5 rows, but when the value is not found it should be empty row.

    So I planned to use Outer Join with the set of values in a temporary table and the above query, something like..

    select table1.* from (1,12,234,42,2) as col1 of temptable left outer join table1 on temptable.col1 = table1.col1

    As there is no write access to the database, these values cannot be loaded into a physical table. Can I get a solution by which this can be done with a temporary logical table within the query?

    Thanks,
    Mohan

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?

    DB2 for LUW supports VALUES clause, like...
    Code:
    SELECT t1.*
     FROM (VALUES 1 , 12 , 234 , 42 , 2) AS tmp(col1)
     LEFT  OUTER JOIN
           table1 AS t1
      ON   t1.col1 = tmp.col1
    If your DB2 doesn't support it, try this
    Code:
     FROM (SELECT   1 FROM sysibm.sysdummy1 UNION ALL
           SELECT  12 FROM sysibm.sysdummy1 UNION ALL
           SELECT 234 FROM sysibm.sysdummy1 UNION ALL
           SELECT  42 FROM sysibm.sysdummy1 UNION ALL
           SELECT   2 FROM sysibm.sysdummy1
          ) AS tmp(col1)

  3. #3
    Join Date
    Apr 2012
    Posts
    12
    Thanks Tonkuma.. the first query is working fine for me..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •