Cross lookup in oracle without creating a table

Question

I have a list of 20 records mapping year to a number from 2001 to 2021. For a couple of reasons these can not be loaded into a table, and I do not have permissions to create temporary tables. This lookup means I can't just run a single query in oracle - I have to export and join with a script. Is there a way I could just do a lookup in memory? I could do a CASE WHEN statement to handle each of the 20 cases. But is there some other smoother way to check values against a list in Oracle when you can't write to a table in between?

Littlefoot Littlefoot · Accepted Answer · 2020-08-04T05:12:51

If I understood you correctly, a CTE might help:

SQL> with years as
  2    (select 2000 + level as year
  3     from dual
  4     connect by level <= 21
  5    )
  6  select year
  7  from years
  8  /

      YEAR
----------
      2001
      2002
      2003
      2004
      <snip>
      2020
      2021

21 rows selected.

SQL>

You'd now join years with other tables, e.g.

with years as 
...
select y.year, e.hiredate
from years y join employees e on e.year = y.year
where ...

Cross lookup in oracle without creating a table

1 Answers