CODEWITHSUNDEEP
sqloracle-databaseplsql
M.Doe
M.Doe

Reputation: 11

How can I have multiple CASE in select statements in PL/SQL function?

I'm trying to write a function that has 2 case statements. For background, a user can have either an A, B, or both A and B (but on separate lines, which is why I can't use a single case statement, unless I use LISTAGG, which I was told not to do for this.

Sample Data:

User State 
1       A 
1       B 
2       A 
3       B

SQL

CREATE OR REPLACE Function F_Calc_State(code Varchar2, id Number, time varchar2) Return Varchar2 AS

    Calc_State(10) := null;

    l_A varchar2(10) := null;
    l_B varchar2(10) := null;

BEGIN

    SELECT CASE WHEN state = 'A' THEN 'A'
                ELSE null 
           END into l_A,   
           CASE WHEN state = 'B' THEN 'B'
                ELSE null 
           END into l_B
      FROM TABLE1
     WHERE state in ('A', 'B')
       AND TABLE1_CODE = code
       AND TABLE1_ID   = id
       AND TABLE1_TIME = time;

    CASE WHEN l_A = 'A' and l_B = 'B' then 'AB'
         WHEN l_A = 'A' THEN 'A'
         WHEN 1_B = 'B' THEN 'B'
         ELSE stafford_recip_ind :='NEITHER';
    END CASE;

    RETURN Calc_State;

EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        RETURN 'NO DATA';
    WHEN OTHERS THEN 
        RETURN SQLERRM ;
END  F_Calc_State;`

For wanted results, when I enter user 1, I want AB to be returned, for user 2 = A, and user 3 = B. I also tried having two different select statement blocks but couldn't get that to work either, it would just hit the exception handler for some reason. Thanks!

Upvotes: 1

Views: 1951

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

The following code would return NULL if no data is found:

CREATE OR REPLACE Function F_Calc_State (
    in_code Varchar2,
    in_id Number,
    in_time varchar2  -- "time" as a string is highly suspicious
) Return Varchar2 
AS
    v_ab varchar2(10) := null;
BEGIN

    SELECT MAX(CASE WHEN t1.state = 'A' THEN 'A' END) ||
           MAX(CASE WHEN t1.state = 'B' THEN 'B' END)
    INTO v_AB
    FROM  TABLE1 t1
    WHERE t1.state in ('A', 'B') AND
          t1.TABLE1_CODE = in_code AND
          t1.TABLE1_ID = in_id AND
          t1.TABLE1_TIME = in_time;    
    RETURN(v_ab);
END;  -- F_Calc_State

However, this does not return return an error if no data is found.

CREATE OR REPLACE Function F_Calc_State (
    in_code Varchar2,
    in_id Number,
    in_time varchar2  -- "time" as a string is highly suspicious
) Return Varchar2 
AS
    v_ab varchar2(10) := null;
BEGIN

    SELECT MAX(CASE WHEN t1.state = 'A' THEN 'A' END) ||
           MAX(CASE WHEN t1.state = 'B' THEN 'B' END)
    INTO v_AB
    FROM  TABLE1 t1
    WHERE t1.state in ('A', 'B') AND
          t1.TABLE1_CODE = in_code AND
          t1.TABLE1_ID = in_id AND
          t1.TABLE1_TIME = in_time;  
    GROUP BY t1.TABLE1_CODE;  -- this will return no rows if there are no matches

    RETURN(v_ab);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN 'NO DATA';
        WHEN OTHERS THEN RETURN SQLERRM ;

END;  -- F_Calc_State

Upvotes: 2

Gaurav
Gaurav

Reputation: 1109

Use below code, Now you will get only a single row of result.

SELECT MAX(CASE WHEN state = 'A' THEN 'A'
                ELSE null END),    
       MAX(CASE WHEN state = 'B' THEN 'B'
                ELSE null END)
INTO l_A, l_B
FROM TABLE1
 WHERE state in ('A', 'B')
   AND TABLE1_CODE = code
   AND TABLE1_ID   = id
   AND TABLE1_TIME = time;

Upvotes: 0

david
david

Reputation: 46

A very trivial approach could be this: It's not clear if you want to check for existance of records or not, as in your exception block you return "NO DATA" while in your case statement you have the value "NEITHER".. anyway, you can adjust this accordingly:

CREATE OR REPLACE Function F_Calc_State( code varchar2
                                       , id number
                                       , time varchar2
                                       ) return varchar2 
as
  l_count_a number;
  l_count_b number;
  l_result varchar2(10);

begin

  select count(*)
    into l_count_a
    from table1
   where state = 'A'
     and table1_code = id
     and table1_time = time;

  select count(*)
    into l_count_b
    from table1
   where state = 'B'
     and table1_code = id
     and table1_time = time;

  if    l_count_a = 0 and l_count_b = 0 then l_result := 'NEITHER'; 
  elsif l_count_a > 0 and l_count_b = 0 then l_result := 'A';
  elsif l_count_a = 0 and l_count_b > 0 then l_result := 'B';
  elsif l_count_a > 0 and l_count_b > 0 then l_result := 'AB';
  else  l_result := '';
  end if;

  return l_result;
end F_Calc_State;

Upvotes: 0

Related Questions