RownuminSELECT

View All Public Scripts Login to Run Script

Script Name RownuminSELECT
Visibility Unlisted - anyone with the share link can access
Description when a select with rownum and order put in SELECT part of the query, receives an error 'MIssing right parenthesis. But when it is wrapped by another select, it is allowed
Contributor giang.doan
Created Tuesday December 13, 2022

Statement 1

Select dummy, 'Just a test rownum and order in SELECT part' acomment, (Select dummy from dual where rownum= 1) ask_case from dual


DUMMY ACOMMENT ASK_CASE
X Just a test rownum and order in SELECT part X

DUMMY	ACOMMENT	ASK_CASE
X	Just a test rownum and order in SELECT part	X

Statement 2

Select dummy, 'Just a test rownum and order in SELECT part' acomment, (Select dummy from dual where rownum= 1 order by dummy) ask_case from dual

ORA-00907: missing right parenthesis

More Details: https://docs.oracle.com/error-help/db/ora-00907

Statement 3

Select dummy, 'Just a test rownum and order in SELECT part' acomment, (Select * from (Select dummy from dual  order by dummy) where rownum= 1) ask_case from dual


DUMMY ACOMMENT ASK_CASE
X Just a test rownum and order in SELECT part X

DUMMY	ACOMMENT	ASK_CASE
X	Just a test rownum and order in SELECT part	X

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database