Samiul Al Hossaini Samiul Al Hossaini - 1 year ago 52
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 Source

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
  select ('action,horror,comedy,adventure,drama,mystery,musical') 
  as genre from dual
  connect by level <=REGEXP_COUNT(genre,'[^,]+')
 select * from movie_genre;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download