select inside a case statement not works

Question

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>

Possible duplicate of [Select inside CASE THEN](https://stackoverflow.com/questions/32203219/select-inside-case-then) — Tripp Kinetics, Apr 09 '18 at 16:50

score 3 · Accepted Answer · answered Apr 09 '18 at 17:22

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.

score 2 · Answer 2 · edited Apr 09 '18 at 16:58

2

In PL/SQL, you'll need to do a SELECT ... INTO. So, to re-write your code:

SET SERVEROUTPUT ON
DECLARE
LV_VAR VARCHAR2(4000):=NULL;
BEGIN

SELECT CASE 
           WHEN 1=1 then 1
                    else 0
       end
INTO LV_VAR
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(LV_VAR);
END;

edited Apr 09 '18 at 16:58

Barbaros Özhan

59,113
10
31
55

answered Apr 09 '18 at 16:53

David Goldman

31
2

score 2 · Answer 3 · answered Apr 09 '18 at 17:22

You are trying to combine PL/SQL Control Statement CASE with SQL CASE Expressions. While it is possible to use a sub-query in THEN return expression of SQL CASE,

select CASE WHEN 1=1 THEN ( select 1 FROM DUAL ) 
ELSE 0 END FROM DUAL;

The same is not true while you use it in PL/SQL although the syntax is same.

Read the Oracle documentation for SQL , CASE1 and PL/SQL : CASE2

select inside a case statement not works

3 Answers3