Oracle: order by subquery missing right parenthesis

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:
copyraw
SELECT
	ps.person_id,
	ps.person_name,
	(
	select 
		pa.person_reference as personid,
		pa.person_text as txt
	from 
		Person_Academia pa
	where 
		pa.type = 'USER' 
		AND pa.code = 'SPECIAL1'
		pa.person_reference = ps.person_reference
	order by
		pa.last_modified_date desc
	)
FROM
	Person_Staff ps

-- In oracle, yields "missing parenthesis" error.
  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:
copyraw
SELECT
	ps.person_id,
	ps.person_name,
	(
		select txt from
		(
			select 
				pa.person_text as txt, 
				pa.person_reference as personid,
				row_number() over (order by pa.last_modified_date desc) r
			from 
				Person_Academia pa
			where 
				pa.type = 'USER' 
				AND pa.code = 'SPECIAL1'
		)
	        where personid = ps.person_id

	) as AcademicInfo
FROM
	Person_Staff ps
  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 
Category: Oracle PL/SQL :: Article: 540

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.