Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle select from dual with multiple rows and columns

I need to join against a dynamic list of numbers that I retrieve from my program dynamically. The number of rows is not fixed, nor are the numbers that are used.

I am not finding a better way to accomplish this than with the following (for my purposes, a temporary table is not helpful):

select 111 as col1, 322 as col2 from dual
union all
select 3 as col1, 14 as col2 from dual
union all
select 56 as col1, 676 as col2 from dual;

Is there a better way to do this? I see that there is a connect by statement that can return multiple rows, but I'm not seeing a way to do multiple rows and columns.

like image 564
Jeremy Avatar asked Jan 10 '23 00:01

Jeremy


1 Answers

You can use the decode and connect by level:

select decode(rownum, 1, 111, 2, 3, 3, 56) as col1,
       decode(rownum, 1, 322, 2, 14, 3, 676) as col2
  from dual
connect by level <= 3;
like image 189
B. Khan Avatar answered Jan 18 '23 03:01

B. Khan