CODEWITHSUNDEEP
sqloracle-databasecase-statement
Moon_Watcher
Moon_Watcher

Reputation: 468

Oracle SQL ORA-00907: missing right parenthesis error in CASE statement

I'm executing a query with the following CASE statement in the select clause:

select (case instr(listagg(D.first_name, ',') 
    within group (order by D.first_name), ',')
when 0 
then substr(listagg(D.first_name, ',') 
    within group (order by D.first_name), 1)
else substr(listagg(D.first_name, ',') 
    within group (order by D.first_name), 1, instr(listagg(D.first_name, ',') 
    within group (order by D.first_name), ',') - 1) end) Advisor1FName
from ....

SQL Developer throws an ORA-00907 missing right paranthesis error.

What is going wrong? Any help would be appreciated.

Upvotes: 1

Views: 34932

Answers (1)

Moon_Watcher
Moon_Watcher

Reputation: 468

Solved by enclosing the entire listagg function in brackets. It's strange that they were required, but that's what solved the problem.

So,

substr(listagg(D.first_name, ',') 
    within group (order by D.first_name), 1)

becomes

substr((listagg(D.first_name, ',') 
    within group (order by D.first_name)), 1)

Upvotes: 2

Related Questions