- Category: Oracle PL/SQL
A quick note on how I got round one this one.
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.
Consider the following:
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.So you can see my parentheses are fine.
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