Oracle SQL - counting to 3

Question

This is an easy one, but I really can't figure it out.

I am trying to get this simple table:

NUMBER
1
2
3

So I try this:

select (1,2,3) as number from dual;

and I get

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

How can I create this static table?

EDIT: Sorry, I simplified my table but should have been more explicit. My numbers are not 1, 2, and 3, they are about 50 numbers somewhere between 1 and 10,000. Sounding like this can't be done?

Define "somewhere". Are you trying to generate random numbers in the specified range? — Branko Dimitrijevic, Apr 02 '12 at 15:39
@BrankoDimitrijevic No, sorry. I have this static list of numbers. — Jeremy, Apr 02 '12 at 15:40

score 3 · Answer 1 · answered Apr 02 '12 at 15:29

3

This "trick" will do it:

select n
from (select rownum n from dual
      connect by level <= 3);

answered Apr 02 '12 at 15:29

Tony Andrews

129,880
21
220
259

Branko Dimitrijevic · Answer 2 · 2012-04-02T15:46:28.233

1

From Oracle 10g onward, you can do:

SELECT LEVEL "NUMBER"
FROM DUAL
CONNECT BY LEVEL <= 3

Obviously, this can be easily modified to generate more numbers than just 3 if needed.

--- EDIT ---

For a static list of numbers, you can simply:

SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
-- etc...

Or, in case you need to use it from more than one place, put the data in a temporary table:

CREATE GLOBAL TEMPORARY TABLE TMP_TABLE (
    "NUMBER" INT PRIMARY KEY
);

INSERT INTO TMP_TABLE VALUES (1);
INSERT INTO TMP_TABLE VALUES (2);
INSERT INTO TMP_TABLE VALUES (3);

SELECT * FROM TMP_TABLE;

edited Apr 02 '12 at 15:46

answered Apr 02 '12 at 15:33

Branko Dimitrijevic

50,809
10
93
167

In fact, I think this has been available from Oracle 2 onward. – Apr 02 '12 at 15:43
@MarkBannister I think the syntax from the Tony Andrews's answer was available before Oracle 10g (though Oracle 2, a product released in 1979, seems a bit too much of a stretch ;) ), but according to [this page](http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques#CONNECT_BY_LEVEL) the syntax from my answer was available from Oracle 10g. OTOH, I'm certainly no Oracle history expert so I might be wrong on this... – Branko Dimitrijevic Apr 02 '12 at 15:51
I don't know why the orafaq.com article says that the syntax in your first query is only available from 10g onwards, unless LEVEL was only available in CONNECT BY clauses (not SELECT clauses) prior to 10g - I don't have an older version of Oracle to hand. As I understand it, DUAL was available from Oracle 1, while the CONNECT BY syntax was introduced in Oracle 2. – Apr 02 '12 at 16:14
@MarkBannister FYI, there was no "Oracle 1". The initial release was "Oracle 2" (as a marketing stunt). BTW, I think it's quite impressive that CONNECT BY was available from Oracle 2! – Branko Dimitrijevic Apr 02 '12 at 16:25

score 0 · Answer 3 · answered Apr 02 '12 at 15:56

I'm not sure what you mean by "How can I create this static table?" When I think of static tables I think of something like:

SQL> create table t (n number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (5000);

1 row created.

SQL> -- ....
SQL> insert into t values (10000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         N
----------
         1
      5000
     10000

To use dual:

SQL> select *
  2  from (select 1 as n from dual
  3      union all select 5000 from dual
  4      -- ...
  5      union all select 10000 from dual);

         N
----------
         1
      5000
     10000

Please do not name the column number. Nothing good can come from using a reserved word for as a column name.

Oracle SQL - counting to 3

3 Answers3