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

Question

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 1

Don't know whether it works in Oracle, but are you looking for something like the following pseudocode?

select t1.*, tempt2.col2 from t1 inner join (select col2, foo, bar from t2 where bar = ?) tempt2 on t1.foo = tempt2.foo where . . .

I guess that doesn't really solve the problem, since you said that table2 (t2) doesn't really exist. I'm not sure what you'd have in your mapping table or where you'd get that data if not from a table.

Answer 2

I'm not totally sure what you're getting at here.

Either what you want is the WITH clause e.g.

WITH tempt2 AS 
  (SELECT x FROM y)
SELECT t1.*, tempt2.col2
FROM t1, tempt2
WHERE ...

Or else if you're running the same SQL on different DBs and you can't be sure that the table actually exists, then you would probably be better to test for its presence and react differently.

Does it need to pure SQL or can you use PL/SQL?

Answer 3

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

Answer 4

I'm not sure this is what you're looking for, but you can do multiple SELECTs with UNIONs to get a derived table

SELECT t1.*, t2.col2
FROM t1, (
    SELECT 1 as Id, 'Foo' as Col2
    UNION ALL
    SELECT 2 as Id, 'Bar' as Col2
    UNION ALL
    SELECT 3 as Id, 'FooBar' as Col2
) t2
WHERE
   t1.Id = t2.Id