Oracle list based queries ORA-00907

This should be helpful in case you get an error missing right parenthesis in oracle when using a list based query.

Needless to say... first check the parenthesis.

Next check if you are using sorting in the subquery.

E.g:

The following will not work:

select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'
order by cell asc
)

order by thedate desc,cmsestab asc


But the following will :


select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'

)

order by thedate desc,cmsestab asc

Enjoy!