Oracle – scalar subquery in if statement Condition in PL/SQL


I have an If Statement block similar to the below which is failing with the error –
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following….

    If (select count(*) from Table1) > 0 then
    end if;

I have similar Case statement which works fine

    when (select count(*) from Table1) > 0
        then 2
from dual

From what i have read in Oracle Documentation the if and when support a Boolean Expression, any ideas whether Subqueries are supported in If Conditions.

Note: The Statements have been simplified, i am not really going to get the count of the entire table, so no optimization suggestions please

Best Solution

No, you can't use a SELECT in the way you want.

In your example using CASE, you are not using a CASE "statement" -- you are using a CASE expression, which happens to be embedded within a SQL statement. You can use a subquery in that case because it's within the context of a SQL statement, not a procedural statement. You wouldn't be able to use a subquery like this in a procedural CASE statement.

Related Question