Oracle case when exists using same variable as insert into

Question

I came across a piece of T-SQL I was trying to convert into Oracle. It looks like this:

SET @local variable= 
    CASE 
        when exists (select field from table where value=0) then 0
        when exists (select same field from same table where value=1) then 1
        when exists (select same fieldfrom same table where value=2) then 1
        else @local variable
    END

The @local variable was set at the result of a query earlier in the procedure.

Now, I tried to convert this to Oracle in the following fashion:

BEGIN
    SELECT CASE
        WHEN EXISTS (
            SELECT field
            FROM table
            WHERE value = 0
        )
        THEN 0
        WHEN EXISTS (
            SELECT same field
            FROM same table
            WHERE value = 1
        )
        THEN 1
        WHEN EXISTS (
            SELECT same field
            FROM same table
            WHERE value = 2
        )
        THEN 1
        ELSE localvariable
    END 
    INTO localvariable
    FROM DUAL;
END;

However, PL/SQL doesn't seem to like me using the @localvariable in the else statement and in the into statement. I'm aware this may not be a well written query to begin with, but what is the solution here? How do I write this in legal PL/SQL?

Answer 1

The CASE and EXISTS cannot be used in the way you expect.

You can do something like this.

DECLARE
    localvariable1 NUMBER;
    localvariable2 NUMBER;
    localvariable3 NUMBER;
    localvariable  NUMBER;
BEGIN

    SELECT COUNT(DECODE(value,'0',field)) as v1,
           COUNT(DECODE(value,'1',field)) as v2,
           COUNT(DECODE(value,'2',field)) as v3 
    INTO
           localvariable1,
           localvariable2,
           localvariable3
    FROM table;

    IF(localvariable1 > 0)
    THEN
        localvariable := 0;
    ELSIF(localvariable2 > 0 OR localvariable3 > 0 )
    THEN
        localvariable := 1;
    END;
END;

Answer 2

I can't assume there is only one record in the table or that the where clause contains other filtering to narrow the result set to one record. The following suggestion duplicates the behavior that if there are multiple rows and the Value field of each may be different, the result will be based on the lowest value found.

declare LocalVar number;
...
select case Min( Value )
         when 0 then 0
         when 1 then 1
         when 2 then 1
         else LocalVar end 
into LocalVar
from SomeTable
where Value between 0 and 2
  and possible_other_criteria;

In fact, the T-SQL query is horribly written. Why make up to three queries against the same table when you can achieve the same results in only one. It could be changed also to pretty much the same query as the PL/SQL. In fact, it looks like the syntax is the same.

Answer 3

Correct way would be to use temporary variable to assign to (tested on Oracle 11.2g):

    DECLARE
     tmp pls_integer;
    BEGIN
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM any_table1
                WHERE value = 0
            )
            THEN 0
            WHEN EXISTS (
                SELECT 1
                FROM any_table2
                WHERE value = 1
            )
            THEN 1
            WHEN EXISTS (
                SELECT 1
                FROM any_table3
                WHERE value = 2
            )
            THEN 1
            ELSE :localvariable
        END 
        INTO tmp
        FROM DUAL;
    :localvariable := tmp;
    END;
    /