Compare variable to a value in a query

Question

I have a cursor that I need to loop through and check a couple conditions. If the person's category is greater than 24, I need it to then see if the person also had a previous record in the table and change the value of the v_check variable if it meets the conditions. The problem is it is saying I can't use a query here. How could I pull this off?

[Error] Compilation (248: 39): PLS-00405: subquery not allowed in this context

IF v_category > 24 THEN 
    IF v_person_id = (
                        SELECT person_id
                        FROM mytable
                        WHERE category = 24
                            AND outcome IS NULL
                            AND person_id = v_person_id
                     ) THEN v_check := 'NO PREV OUTCOME';                                      
    ELSIF v_person_id = (
                            SELECT person_id
                            FROM mytable
                            WHERE category = 24
                                AND outcome_date IS NULL
                                AND person_id = v_person_id
                        ) THEN v_check := 'NO OUTCOME DATE';
    ELSIF category = 24 THEN v_check := 'N/A';
    END IF;
ELSE v_check := 'OK';  
END IF;

score 1 · Answer 1 · answered Jul 01 '21 at 16:41

Main problem is that you can't use a subquery that way; first find value(s) returned by the select statement(s), then use them in case.

Also, code you posted doesn't make much sense as

you use v_person_id as a variable (to store a value into) and - at the same time - in select's where clause
the first if checks whether v_category > 24, while the last elsif says that if v_category = 24 then 'N/A' - that's never going to happen.

Here's an example which show how to do it; it probably won't work because I don't know what exactly you want to do (because of notes I posted above), but I hope that it'll get you started.

declare
  v_person_id  number := some value
  
  l_person_id  number;
  l_person_uid number;
begin  
  select person_id, person_uid
  into l_person_id, l_person_uid
  from mytable
  where category = 24
    and outcome is null
    and person_id = v_person_id;
  
  v_check = case when v_category > 24 and v_person_id = l_person_id  then 'NO PREV OUTCOME'
                 when v_category > 24 and v_person_id = l_person_uid then 'NO OUTCOME DATE'
                 else 'N/A'
            end;               
end;

Compare variable to a value in a query

1 Answers1