Oracle Dynamic Join Challenge

Question

EDIT: Far simpler example. (Formerly titled: Oracle Column Injection)

GOAL: Complete the query below generate the following results?

PURPOSE: Create a column dependent on an existing column in a table without putting the table in a subquery.

Rules:

1. Restructuring the query to put tbl in a subquery is not an option.
2. The query must use a,b->1; x->2; y->3, not simply join to 1,1,2,1,3.
3. The subquery tbl must not be modified.

 

SELECT val, cat
  FROM (SELECT 'a' val FROM DUAL  UNION ALL
        SELECT 'b' val FROM DUAL  UNION ALL
        SELECT 'x' val FROM DUAL  UNION ALL
        SELECT 'b' val FROM DUAL  UNION ALL
        SELECT 'y' val FROM DUAL) tbl
   ... JOIN ( ... ) ON ...

 val | cat
-----+-----
 'a' |  1
 'b' |  1
 'x' |  2
 'b' |  1
 'y' |  3

Well, I got this far (below), but I cannot add a second LEFT JOIN.

SELECT val, cat
  FROM (SELECT 'a' val FROM DUAL  UNION ALL
        SELECT 'b' val FROM DUAL  UNION ALL
        SELECT 'x' val FROM DUAL  UNION ALL
        SELECT 'b' val FROM DUAL  UNION ALL
        SELECT 'y' val FROM DUAL) tbl
   LEFT JOIN ( SELECT 1 cat FROM DUAL ) ON val in ('a','b')

 val | cat
-----+-----
 'a' |  1
 'b' |  1
 'x' |  
 'b' |  1
 'y' |

Answer 1

SELECT tbl.val, map.cat
  FROM (SELECT 'a' val FROM DUAL  UNION
        SELECT 'b' val FROM DUAL  UNION
        SELECT 'x' val FROM DUAL  UNION
        SELECT 'b' val FROM DUAL  UNION
        SELECT 'y' val FROM DUAL) tbl
   LEFT JOIN ( SELECT 'a' val, 1 cat FROM DUAL UNION
               SELECT 'b' val, 1 cat FROM DUAL UNION
               SELECT 'x' val, 2 cat FROM DUAL UNION
               SELECT 'y' val, 3 cat FROM DUAL ) map ON map.val = tbl.val

From your examples, I suspect you mean to use UNION ALL instead of UNION (in order to have 2 rows for val = 'b' appear in the result).

Your "I got this far" example doesn't even run as written -- it needs parentheses around the IN list -- and even once you fix that it doesn't produce the output you show.