Samiul Al Hossaini Samiul Al Hossaini - 1 month ago 8
SQL Question

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

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

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;
Comments