comparing a variable in when oracle sql

Question

I want to use a variable in when clause of oracle sql like below

DEFINE balancing_id = 0--'16493'--null;
ON A.BALANCING_ID = 
CASE
    WHEN &balancing_id is null-- &balancing_id = ''--balancing_id>0
    THEN B.BALANCING_ID 
    ELSE &balancing_id
END

But I get the error below :

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"
*Cause:    
*Action:

this line:

WHEN &balancing_id is null-- &balancing_id = ''--balancing_id>0

UPDATE:

WHEN 1 = 1

works as expected

How to use variable like this?

score 0 · Answer 1 · answered Jul 15 '23 at 16:24

Consider what will happen in the case where &balancing_id is null. The SQL condition will be evaluated as

WHEN  is null

If it's possible for &balancing_id to be null, then you will have to either set it to the word null or else treat it as a quoted string and then convert it to a number explicitly.

SQL> define balancing_id = null
SQL>
SQL> set verify on
SQL>
SQL> select count(*) from dual where &balancing_id = 0;
old   1: select count(*) from dual where &balancing_id = 0
new   1: select count(*) from dual where null = 0
COUNT(*)
     0

or

SQL> define balancing_id = ''
SQL>
SQL> define balancing_id
DEFINE BALANCING_ID    = "" (CHAR)
SQL>
SQL> select count() from dual where to_number('&balancing_id') = 0;
old   1: select count() from dual where to_number('&balancing_id') = 0
new   1: select count(*) from dual where to_number('') = 0
COUNT(*)
     0

comparing a variable in when oracle sql

1 Answers1

COUNT(*)

COUNT(*)