How to create a temporary / dynamic / virtual table when a SQL runs in Oracle?

Question

I have some data that has to be measured which are not in any table. I can not insert it to a table nor I can create any table and insert these data. So I used dual like the following to get that table. I used this to join with other tables.

with movie_genre as
(
select '10' as "id", 'action' as "genre" from dual
union select '20' as "id", 'horror' as "genre" from dual
union select '30' as "id", 'comedy' as "genre" from dual
union select '40' as "id", 'adventure' as "genre" from dual
union select '50' as "id", 'drama' as "genre" from dual
union select '60' as "id", 'mystery' as "genre" from dual
union select '70' as "id", 'musical' as "genre" from dual
)
select * from movie_genre
;

So that I get the result -

id  genre
10  action
20  horror
30  comedy
40  adventure
50  drama
60  mystery
70  musical

My question is, is there any better way to do this? Any suggestion will be a life saver.

An example -

Lets say we have a table -

create table movies (
id number,
name varchar2(50),
genre_id number
);

insert into movies values (1, 'the hulk', 10);
insert into movies values (2, 'dumb and dumber', 30);
insert into movies values (3, 'frozen', 70);

And we need a result like this -

name                genre        is_in_genre
the hulk            action       yes
the hulk            horror       no
the hulk            comedy       no
the hulk            adventure    no
the hulk            drama        no
the hulk            mystery      no
the hulk            musical      no
dumb and dumber     action       no
dumb and dumber     horror       no
dumb and dumber     comedy       yes
dumb and dumber     adventure    no
dumb and dumber     drama        no
dumb and dumber     mystery      no
dumb and dumber     musical      no
frozen              action       no
frozen              horror       no
frozen              comedy       no
frozen              adventure    no
frozen              drama        no
frozen              mystery      no
frozen              musical      yes

Here, we DO NOT have any movie_genre table.

Answer 1

Not sure if this works for Oracle, but for SQL Server there is a neater implementation.

Example: SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

Ref: Section C. Specifying multiple values as a derived table in a FROM clause

https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql

Answer 2

The movie_genre table can also be created by the following -

with movie_genre as
(
  select i, genre
  from 
  ( select '1000' id, 'length-of-the-longest-value' genre from dual )
  where id <> 1000
  model
  dimension by (genre)
  measures (id i)
  rules
  (
    i['action'] = 10,
    i['horror'] = 20,
    i['comedy'] = 30,
    i['adventure'] = 40,
    i['drama'] = 50,
    i['mystery'] = 60,
    i['musical'] = 70
  )
  order by i
)
select * from movie_genre;

What do you think about this?? Is it more efficient than doing union on all the dual tables?

Answer 3

You can pass the genre as string in the order you want and use regular expression to generate the movie_genre table.The sql fiddle here

with movie_genre as
( 
 select level * 10 as id, regexp_substr(genre,'[^,]+',1,level) as genre
 from
 (
  select ('action,horror,comedy,adventure,drama,mystery,musical') 
  as genre from dual
  )
  connect by level <=REGEXP_COUNT(genre,'[^,]+')
 )
 select * from movie_genre;

Answer 4

Thus you can use DECODE function. Something like this:

select 
  m.*,
  decode(m.genre_id, 10, 'action', 20, 'horror', ...) as genre
from 
  movies m

If you wish to generate you strange formed resultset, you can use, say

select
  m.name,
  decode(n.id, 10, 'Action', ...) genre,
  case when m.genre_id = n.id then 'yes' else 'no' end is_in_genre
from
  movies m,
  (select 10 * rownum id from dual connect by level <= 7) n

More common way is to eliminate movies_genre at all:

select
  m.name,
  case when m.genre_id = 10 then 'yes' else 'no' end is_action,
  case when m.genre_id = 20 then 'yes' else 'no' end is_horror,
  ....
from
  movies m
from
  movies m,
  (select 10 * rownum id from dual connect by level <= 7) n

Answer 5

If you need to get the full list, you want a cross join, with some additional logic:

with movie_genre as (
      select '10' as "id", 'action' as "genre" from dual union all
      select '20' as "id", 'horror' as "genre" from dual union all
      select '30' as "id", 'comedy' as "genre" from dual union all
      select '40' as "id", 'adventure' as "genre" from dual union all
      select '50' as "id", 'drama' as "genre" from dual union all
      select '60' as "id", 'mystery' as "genre" from dual union all
      select '70' as "id", 'musical' as "genre" from dual
     )
select m.name, mg.genre,
       (case when m.genre_id = mg.id then 'yes' else 'no' end) as IsInGenre
from movies m cross join
     movie_genres mg;