We're seeing the error message ORA-00936 Missing Expression for the following SQL:
Note that this is just a cut-down version of a much bigger SQL so rewriting it to a inner join or similar is not really in the scope of this:
This is the SQL that fails:
select (select count(*) from gt_roster where ROS_ROSTERPLAN_ID = RPL_ID)
from gt_rosterplan
where RPL_ID = 432065061
What I've tried: * Extracting the innermost SQL and substituting the ID from the outer SQL gives me the number 12. * Aliasing both the sub-query, and the count(*) individually and both at the same time does not change the outcome (ie. still an error)
What else do I need to look at?
The above are only tables, no views, RPL_ID is primary key of gt_rosterplan, and ROS_ROSTERPLAN_ID is a foreign key to this column, there is basically no magic or hidden information here.
Edit: In response to answer, no, you do not need the aliases here as the columns are uniquely named across the tables.
Solved: The problem was that the client was running the wrong client driver version, 9.2.0.1, and there are known problems with that version.
The problem was that the client was running the wrong client driver version, 9.2.0.1, and there are known problems with that version.