CODEWITHSUNDEEP
sqloracle-database
Bobby
Bobby

Reputation: 584

Is it possible to have multiple rows with SELECT FROM DUAL?

Can I have something like

SELECT (1, 2, 4542, 342) FROM DUAL;

and get it like this?

  |  1    |
  |  2    |
  |  4542 |
  |  342  |

Upvotes: 15

Views: 26848

Answers (7)

Jon Heller
Jon Heller

Reputation: 36817

Before 23c: Instead of DUAL, select from a pre-built collection to return multiple rows. This solution has a small syntax, avoids type conversions, and avoids potentially slow recursive queries. But it's good to understand the other solutions as well, since they are all useful in different contexts.

select column_value from table(sys.odcinumberlist(1, 2, 4542, 342));

Note that the TABLE() operator is optional in modern versions of Oracle, and it is only required in older versions.

23c or later: The table values constructor, VALUES, provides an easier way to return multiple rows. This feature does not require any predefined collection, and it can also be used to return multiple columns per row if desired.

select * from (values (1), (2), (4542), (342)) a (id);

Upvotes: 17

dougals
dougals

Reputation: 1

Other example whit alias:

SELECT column_value as xvalue FROM sys.odcinumberlist ( 1, 2, 4542, 342 ) ;

Upvotes: 0

user9135988
user9135988

Reputation:

Dual table can have multiple rows and multiple columns, for example =>

SQL> select 1, 'john' from dual
  2  union
  3  select 2, 'smith' from dual;

         1 'JOHN
---------- -----
         1 john
         2 smith

Upvotes: 3

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

select decode(level,3,4542,4,342,level) lvl from dual connect by level <= 4

Upvotes: 9

SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

Dual table is basically a dummy table which is having one row and one column and is exists in sys schema

SELECT (1,2, 4542, 342) FROM DUAL;

This will give you an error because you have passed more than one column in this table. 1,2,4542,342 treated as separate column. Try the below Query:-

SELECT ('1,2, 4542, 342') FROM DUAL;

'1,2, 4542, 342' will treated as a single string.

For your required output we can use UNION ALL operator. Try the below query:-

SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 4542 FROM DUAL UNION ALL SELECT 342 FROM DUAL;

Upvotes: 2

APC
APC

Reputation: 146239

Well if (1, 2, 4542, 342) were a string you could do this:

with cte as (
    SELECT '1, 2, 4542, 342' as str 
    FROM DUAL
)
select regexp_substr(str,'[^,]+',1,level) 
from cte
connect by level <= regexp_count(str, ',')+1
/

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269913

No. dual has just one row, but you can use union all:

SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 4542 FROM DUAL UNION ALL
SELECT 342 FROM DUAL;

This is just one way to generate a table "on-the-fly" in Oracle.

Upvotes: 9

Related Questions