SQL Statement giving Error ORA-00907

[tammyng]

I'm trying out a SQL statement syntax which works in my friend's system but failed at my system..
wonder why...

syntax
select * from
( select a.*, rownum as rnum from
( select * from table1
order by pcno ) a
where rownum < = 5)
where rnum >= 1

anyone knows why am i getting the Error :
ORA-00907: missing right parenthesis

 

[rcurva]

Remove ORDER BY from the embedded SQL:

select *
from
( select a.*, rownum as rnum
from
( select *
from table1 ) a
where rownum < = 5 )
where rnum >= 1;





Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War

 

[tammyng]

hi Robbie,
Thanks for the suggestion, but i need the result to be in
ascending order of field, pcno...
if i remove the ..order by .. the result is different..

hope u can help ... )

 

[karluk]

Unfortunately, the &quot;order by&quot; clause in the subselect is simply not supported in Oracle 8.0.x. That's probably why it works on your friend's system but not on yours: you are running different versions of Oracle.

One unusual detail is the situation in Oracle 8.1.5. The syntax was supported in SQL, but not in PL/SQL. Therefore whether it worked or not depended on exactly what you were doing.

This issue was discussed in some detail in thread186-34165. Please read it for more info.

I'm afraid that if you're not on at least Oracle 8.1.5 you're out of luck. I know of no way to get your query to work on earlier versions of Oracle.

 

[tammyng]

hi karluk,
yeha... i've checked with the administrator.. and
too bad.. it's Oracle 7.3.4...

now i know why it doesn't at my system..

thanks.