jeudi 1 juillet 2021

Compare variable to a value in a query

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_uid
                            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;

Aucun commentaire:

Enregistrer un commentaire