laurie laurie - 4 years ago 194
SQL Question

SQL create a temporary 'mapping' table in a select statement

I'm building up results by joining tables

select t1.*, t2.col2 from t1, t2 where t1.col1=t2.col1


Is there a way to create a temporary 'mapping' table 'inline' in a select statement for instances where the t2 table doesn't exist?

So something like

select t1.*, tempt2.col2 from t1, (<create temp table>) tempt2 where ...


I'm using Oracle

Answer Source

Table with 1 row:

SELECT t1.*, t2.col2
FROM   t1,
       (
       SELECT  1 AS col2
       FROM    dual
       ) t2

Table with 0 rows:

SELECT t1.*, t2.col2
FROM   t1,
       (
       SELECT  1 AS col2
       FROM    dual
       WHERE   1 = 0
       ) t2

Table with N rows:

SELECT t1.*, t2.col2
FROM   t1,
       (
       SELECT  1 AS col2
       FROM    dual
       CONNECT BY
               level <= :N
       ) t2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download