Home » SQL & PL/SQL » SQL & PL/SQL » Query issue
Query issue [message #224667] Thu, 15 March 2007 01:42 Go to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25
minus
select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25

when i type the above query it gives me 16 rows.I think it should return null.


select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 100.

if i type the above query it gives me 25 rows andwhen i do a rowcount ,it gives me 100. Isn't it strange?Can anybody help me?

[Updated on: Thu, 15 March 2007 01:43]

Report message to a moderator

Re: Query issue [message #224693 is a reply to message #224667] Thu, 15 March 2007 02:57 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

what is your database version
in 10g it gives no rows selected
Re: Query issue [message #224696 is a reply to message #224667] Thu, 15 March 2007 03:05 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

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

SQL> select level from dual connect by level < 10;

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



and check your array size
show arraysize
Re: Query issue [message #224697 is a reply to message #224667] Thu, 15 March 2007 03:06 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

check this link
Re: Query issue [message #224698 is a reply to message #224696] Thu, 15 March 2007 03:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Dual is magic.
Dual in combination with sqlplus is magic. (sqlplus 'knows' there is only 1 row in dual).
Rewrite your query to a select * from (select .. from dual connect...)
Re: Query issue [message #224720 is a reply to message #224698] Thu, 15 March 2007 04:25 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

my version is Oracle 9i.
It's still confusing me.

If i try it in 10g it says no rows selected.
Re: Query issue [message #224969 is a reply to message #224720] Fri, 16 March 2007 03:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In 9i, the syntax
select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25
only works properly if you use it as an inline view, like this
select * from (select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25)


Example, under SQL*Plus (9i)
SQL> select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25;

MULT_                                                                                      NUM
----------------------------------------------------------------------------------- ----------
5*1=5                                                                                        1
Under SQL Developer
"MULT_"	"NUM"
"5*1=5"	"1"
"5*2=10"	"2"
"5*3=15"	"3"
"5*4=20"	"4"
"5*5=25"	"5"
"5*6=30"	"6"
"5*7=35"	"7"
"5*8=40"	"8"
"5*9=45"	"9"
"5*10=50"	"10"


If you rewrite your query like this, then you get the correct results:
SQL> select * from (select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25)
  2  minus
  3  select * from (select '5'||'*'||rownum||'='|| 5 * rownum mult_,rownum num from dual connect by level <= 25);

no rows selected
Previous Topic: How to create a procedure in a package body
Next Topic: return a type record in a function??
Goto Forum:
  


Current Time: Mon Dec 05 07:02:03 CST 2016

Total time taken to generate the page: 0.15456 seconds