Constructing A Temporary Table In Oracle Sql

I am trying to make a sub-table that 'stores' a decode between two values, because I need to use that decode multiple times. Let's say these are my tables: Table Person Name Num

Solution 1:

The WITH clause is sounds like the closest thing to what you're describing. But that requires that you generate the data somehow. Selecting from DUAL is likely the easiest option

WITH my_temp_table AS (
  SELECT'One' name, 1 num from dual unionallSELECT'Two', 2from dual unionallSELECT'Three', 3from dual unionallSELECT'Four', 4from dual
)
SELECT*FROM my_temp_table 
       JOIN person ON (<<somejoincondition>>)
 WHERE<<some predicate>>

Since you don't want to union a bunch of queries, you could do something like

WITH my_temp_table AS (
  select level num,
         initcap( to_char( to_date( level, 'J' ),'JSP' )) namefrom dual
 connect by level <= 4
)
...