Home » SQL & PL/SQL » SQL & PL/SQL » How to list records in oracle/sql?
How to list records in oracle/sql? [message #269416] Sat, 22 September 2007 03:33 Go to next message
srikanthvijay8
Messages: 16
Registered: July 2007
Location: Chennai pattanam
Junior Member
hi
my name is srikanth. i have one scenario based question,
consider emp table consists of only one column called empno.
there are 4 records exists such as
2 5 6 9
expected output is:
1 3 4 7 8
write sql query for this.
note that output is of records only, not columns.
does not any other view /dml for this.

if you know answer mail to
msc_it_srikanth@yahoo.com

[Updated on: Sat, 22 September 2007 03:55]

Report message to a moderator

Re: How to list records in oracle/sql? [message #269418 is a reply to message #269416] Sat, 22 September 2007 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome srikanth.
May I suggest you to read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Providing a test case is a good way to have a quick answer.
Please always post your Oracle version (4 decimals).

Explaining what you want also. Providing a result is useful to understand an explaination but does not replace it.
It seems you want the values that are NOT in the table, in this case search for "calendar" or "connect by level" in this site you'll have many examples of how to generate rows.
Come back if you don't find a solution.
Come back if you find a solution to post it for others.

Regards
Michel
Re: How to list records in oracle/sql? [message #269419 is a reply to message #269418] Sat, 22 September 2007 03:47 Go to previous messageGo to next message
srikanthvijay8
Messages: 16
Registered: July 2007
Location: Chennai pattanam
Junior Member
i have one questions .
consider emp table contains empno column.
consists of 4 records such as 1 4 5 8

but expected output is:
2 3 6 7 9
write sql for this?
how to write? please note that you dont use pl/sql or
view for this. all these records are not columns. only rows.

[Updated on: Sat, 22 September 2007 03:54]

Report message to a moderator

Re: How to list records in oracle/sql? [message #269423 is a reply to message #269419] Sat, 22 September 2007 03:54 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat the question? (By the way, you repeated it twice but I deleted one of the two).
Follow what I said.

And if you want the result on one line, after you get it on multiple lines, search for "pivot".

Regards
Michel

[Updated on: Sat, 22 September 2007 03:54]

Report message to a moderator

Previous Topic: Maybe an impossible join
Next Topic: Uses of materialized views?
Goto Forum:
  


Current Time: Thu Dec 08 16:23:58 CST 2016

Total time taken to generate the page: 0.05266 seconds