Create a table from DUAL

Question

How can I create the following table in Oracle.

+------+------+
| col1 | col2 |
+------+------+
| A    |    1 |
| B    |    2 |
+------+------+

I need it as an itermediate table in a WITH-clause.

score 2 · Answer 1 · answered Jul 25 '20 at 17:38

2

You can use:

with t as (
      select 'A' as col1, 1 as col2 union all
      select 'B' as col1, 2 as col2
     )

You can then use t throughout the rest of the query.

answered Jul 25 '20 at 17:38

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jul 25 '20 at 17:43

1

Please use below query,

with tbl as 
  (
   select 'A' as col1, 1 as col2 from dual
   UNION
   select 'B' as col1, 2 as col2 from dual
)

select * from tbl;

answered Jul 25 '20 at 17:43

Jim Macaulay

4,709
4
28
53

score 1 · Answer 3 · answered Jul 25 '20 at 19:35

When you create a common table expression such as this you can name the columns in the CTE definition, which saves having them scattered through the SELECT statement:

WITH cteWork (COL1, COL2) AS
  (SELECT 'A', 1 FROM DUAL UNION ALL
   SELECT 'B', 2 FROM DUAL)
SELECT *
  FROM cteWork

db<>fiddle here

Create a table from DUAL

3 Answers3