Home » SQL & PL/SQL » SQL & PL/SQL » Level Pseudo Column
Level Pseudo Column [message #248664] Sat, 30 June 2007 01:59 Go to next message
manoj_vilayil
Messages: 9
Registered: May 2007
Location: India
Junior Member
Sir,
I want to get a range of date from a single query.I tries it using LEVEL pseudo column.But maximum no: the level reach is 25.I use oracle 9i version.Plz reply

select sysdate+level
from dual
connect by level<100


Thanks in advance
Manoj

[Updated on: Sat, 30 June 2007 02:05]

Report message to a moderator

Re: Level Pseudo Column [message #248665 is a reply to message #248664] Sat, 30 June 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.
If the query was correct it should work.

Regards
Michel
Re: Level Pseudo Column [message #248666 is a reply to message #248665] Sat, 30 June 2007 02:07 Go to previous messageGo to next message
manoj_vilayil
Messages: 9
Registered: May 2007
Location: India
Junior Member
select sysdate+level
from dual
connect by level<100
Re: Level Pseudo Column [message #248668 is a reply to message #248664] Sat, 30 June 2007 03:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just a guess: you call this script in toad and it is not displaying the full result set.
Consult Can there be an infinite DUAL? thread on AskTom, it contains more information.
Re: Level Pseudo Column [message #248670 is a reply to message #248666] Sat, 30 June 2007 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Manoj,

This does not work in 9i.

Regards
Michel
Re: Level Pseudo Column [message #248672 is a reply to message #248670] Sat, 30 June 2007 04:29 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel !! could you please let us know why it is not working ...
Re: Level Pseudo Column [message #248684 is a reply to message #248672] Sat, 30 June 2007 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just because 9i did/does not support it!

Regards
Michel
Re: Level Pseudo Column [message #249022 is a reply to message #248684] Tue, 03 July 2007 01:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In the Asktom thread mentioned above, Tom Kyte explains it is a SQL*plus feature of 9iR2. I believe this is not true. When I test it on my SQL*Plus release 10.2.0.1 it does the same thing when connected tot a 9i database:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 3 08:40:53 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> SELECT level
  2  FROM   dual
  3  CONNECT BY level < 100
  4  /

     LEVEL
----------
         1

SQL>
But, when I connect to a 10g with the same SQL*Plus executable, it does produce 99 rows. So I think (note that this is a personal observation and assumption) that this is a feature of the optimizer. I also connected my SQL*plus 9i to a 10gR2. Then I launched a connect by query on dual:
C:\Oracle\ORA92\bin>sqlplus mhe@xe

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jul 3 08:47:21 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SELECT level
  2  FROM   dual
  3  CONNECT BY level < 11
  4  /

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL>

This confirms my previous findings: the root cause appears to be the 9i database rather than the SQL*Plus.

We can continue to talk about it, but in the end I have to agree with Michel: 9i does not support it. You can use the following workaround:
select thedate
from ( select sysdate + level thedate
       from   dual
       connect by level < 100
     )


MHE
Previous Topic: outer join
Next Topic: O/S Executable file
Goto Forum:
  


Current Time: Tue Dec 06 00:09:52 CST 2016

Total time taken to generate the page: 0.07460 seconds