SQL (Oracle) Select without an actual table containing static data

Question

I am looking for the possibility of running SQL (Oracle) without querying the actual table. Here I found a tip with a DUAL table . It is very good. But I'm looking for a short solution to select MULTIPLE strings from "nowhere." Is it possible? Here is the shortest query I can think of that does what I need:

Select 1, 'foo' FROM DUAL union Select 2, 'bar' FROM DUAL union Select 3, 'more stuff' FROM DUAL 

But if I want to have more lines in my result, it becomes rather inconvenient. Is there a shorter way? Sort of

 Select 1, 'foo'; 2, 'bar'; 3, 'more stuff' from dual or Select * from (1, 'foo') union (2, 'bar') union (3, 'more stuff') 

I know this doesn't work, but do you have an idea? Is there an easy way to transpose the result of queries? Sort of:

 Select transposed (1, 'foo', 2, 'bar', 3, 'more stuff') from dual 

I want to keep the number of "service characters" at the lowest level.

Answer 1

You can use connect by level to create more entries - something like:

 select level, decode(level, 1,'foo', 2,'bar', 3,'more stuff') from dual connect by level <= 3 

Answer 2

If there are multiple columns - then this also works -

 select REGEXP_SUBSTR (jango,'^[^#]*') as f1, REGEXP_SUBSTR(REGEXP_REPLACE(jango,'^([^#]*#){1}', ''),'^[^#]*') as f2, REGEXP_REPLACE(jango,'^([^#]*#){2}', '') as f3 from ( Select decode(level, 1, 'foo#koo#joo', 2, 'bar#loo#too' , 3, 'more stuff#doo#dingo') as jango from dual connect by level <= 20 ) 

Here's how it works. The internal query is the same as above. I added some columns using # - we need to make sure that it is not part of the regular expression family, and we need to avoid it.

  Select decode(level, 1, 'foo#koo#joo', 2, 'bar#loo#too' , 3, 'more stuff#doo#dingo') as jango from dual connect by level <= 20 

Gives the following -

  Jango ------------------- foo#koo#joo bar#loo#too more stuff#doo#dingo 

Now the next fragment is selected from the output column - "jango", anything up to #

  REGEXP_SUBSTR (jango,'^[^#]*') as f1, O/p --> foo 

for the second column, we delete the contents of the 1st column, followed by #

  REGEXP_REPLACE(jango,'^([^#]*#){1}', '') we get --> koo#joo 

Now the first step is to get the first field.

for more fields {1} can be increased.