Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Articles // SQL // Oracle PL/SQL

Oracle: order by subquery missing right parenthesis

Thursday, 5th December 2013
17,157 Reads
What?
A quick note on how I got round one this one.

Why?
Often enough, our requirement is that the latest record from another table is associated with the current row, and often enough we get the latest by ordering the dataset of the subquery. In T-SQL and MySQL, this is not so much of an issue.

I get this error when having to use an ORDER BY clause in a subquery within an Oracle 11g environment.

How?
Consider the following:
  1. SELECT  
  2. ps.person_id,  
  3. ps.person_name,  
  4. (  
  5. select  
  6. pa.person_reference as personid,  
  7. pa.person_text as txt  
  8. from  
  9. Person_Academia pa  
  10. where  
  11. pa.type = 'USER'  
  12. AND pa.code = 'SPECIAL1'  
  13. pa.person_reference = ps.person_reference  
  14. order by  
  15. pa.last_modified_date desc  
  16. )  
  17. FROM  
  18. Person_Staff ps  
  19.  
  20. -- In oracle, yields "missing parenthesis" error.
So you can see my parentheses are fine.

Working Solution:
  1. SELECT  
  2. ps.person_id,  
  3. ps.person_name,  
  4. (  
  5. select txt from  
  6. (  
  7. select  
  8. pa.person_text as txt,  
  9. pa.person_reference as personid,  
  10. row_number() over (order by pa.last_modified_date desc) r  
  11. from  
  12. Person_Academia pa  
  13. where  
  14. pa.type = 'USER'  
  15. AND pa.code = 'SPECIAL1'  
  16. )  
  17. where personid = ps.person_id  
  18.  
  19. ) as AcademicInfo  
  20. FROM  
  21. Person_Staff ps


Article Comment (1)

Friday, 20th January 2017
0 Votes
Gravatar for Art
Art
Hello,
what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?


Recent Comments

Gravatar for Art
Oracle: order by subquery missing right parenthesis
Hello, what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?

20 Jan


Gravatar for Robert
CharIndex Reverse - find occurrence starting from end of string in TSQL
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks. SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

29 Dec


Gravatar for Translation

27 Dec


Gravatar for Tibbe
JComments 2.3.0 with ReCaptcha in Joomla 2.5.x
Hi there, This looks like a great solution to get rid of spam comments. How to integrate this in Joomla 3.x? Step 2 I did in settings.xml, but no result in frontend. Kind Regards, Tibbe

16 Dec


Gravatar for sach|n
SSRS Repeat Headers in PDF Report
Yes its working for me..thnx Joel.

7 Oct