File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Serial number in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Serial number in SQL" Watch "Serial number in SQL" New topic
Author

Serial number in SQL

Raghu Devatha
Ranch Hand

Joined: Feb 10, 2009
Posts: 39
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



I tried something like



dR
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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



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

Joined: May 22, 2002
Posts: 51

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.




where table looks like
Raghu Devatha
Ranch Hand

Joined: Feb 10, 2009
Posts: 39
Thanks. It worked.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Serial number in SQL