I wanted to write a select statement inside CASE THEN statement in PLSQL but it throws error. Please advise if I could write select statement inside THEN Statement.
An example similar to my requirement looks like below
SET SERVEROUTPUT ON
DECLARE
LV_VAR VARCHAR2(4000):=NULL;
BEGIN
LV_VAR:= CASE
WHEN 1=1 THEN
(SELECT 1 FROM DUAL)
ELSE
0
END;
DBMS_OUTPUT.PUT_LINE(LV_VAR);
END;
While executing , it throws error as below
ORA-06550: line 6, column 26:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
You can't use scalar subqueries directly in PL/SQL code, like you have shown. (Of course, you knew that already.) You must select the value INTO a variable, and then use it.
ALSO: You have no case statements in your code. You have a case expression. It just won't work quite the way you wrote it.
One alternative is to use a case expression within the SQL SELECT ... INTO statement, as David Goldman has shown in his Answer.
However, if the whole point of your exercise was to practice case expressions as used in PL/SQL, not inside a SQL statement, you would need to SELECT ... INTO a variable you declare in your code, and then use that variable in the case expression. Something like this:
DECLARE
LV_VAR VARCHAR2(4000);
BEGIN
SELECT 1 INTO LV_VAR FROM DUAL;
LV_VAR:= CASE
WHEN 1=1 THEN
LV_VAR
ELSE
0
END;
DBMS_OUTPUT.PUT_LINE(LV_VAR);
END;
As you can see, I did something that is done frequently in procedural language code: Instead of declaring and using TWO variables, I only declared one. I populated it with the result of the SELECT ... INTO query. Then I assigned to it again in the case expression: in one case I assign it to itself and in the other I assign to it the value 0.