Serial number in SQL

[Raghu Devatha]

Hello,

I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50.

The table may not have all the Sequence number. For example following is the data in my table..

NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3

I need a SQL to get the data like

NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50

Basically I want to have rows for the sequence numbers which are not present i my table.

I thought of using the following query to generate a sequence and then join, but does not help

select rownum from dual connect by rownum <=50

I tried something like

with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence ) select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num

[Martin Vashko]

Raghu Devatha wrote:
I thought of using the following query to generate a sequence and then join, but does not help

select rownum from dual connect by rownum <=50

That would be my first approach too. What exactly did you try and in which way did it "not help"?

[Gene Hilpert]

You need an outer join on your table with data in it.
Sorry the style is the old join the (+) is put on the table that is missing the data.

SELECT a.ID, t.NAME
  FROM testuser.TEST1 t
      ,(    SELECT ROWNUM id, '' name
              FROM DUAL
        CONNECT BY ROWNUM < 10) a
 WHERE t.id(+) = a.id
 order by a.ID;

where table looks like

CREATE TABLE testuser.TEST1
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE)
)

[Raghu Devatha]

Thanks. It worked.