CODEWITHSUNDEEP
sqloracle-databaseplsqlcase
Gadget8Bit
Gadget8Bit

Reputation: 3

PLSQL Case Statement Comparing Two Columns

I'm struggling with a case statement as part of a PL/SQL script.

The requirement is that we have an ETL job that runs loading a single row to a table with the results. This job returns two columns for different segments that will each read with a pass or a fail. The results of those columns are emailed to the team responsible for this data. So if they both read "Pass", they get an email to say everything is a-ok. If either column reads "Fail" they get an email to say that they need to check the data.

When I'm testing this script it's simply not working, either reporting that the SQL command isn't ended properly or throwing up a host of PL/SQL errors. Because there will be more than one row of data in the table, the two select sub queries each look for the latest row, hence the MAX(LOAD_KEY) sections.

Have I made this overcomplicated? Have I over thought this?

whenever sqlerror exit failure rollback
--
connect / as sysdba
set serverout on size 1000000
--set echo on
--
alter session set current_schema = &2;
--
--
-- executing function
--
DECLARE
  RetVal  Number;
--
--
-- Retrieve latest outcome records

--
BEGIN
 select case 
    when (SELECT T4_VALIDATION_OUTCOME 
 FROM TABLE 
 WHERE LOAD_KEY = (select MAX(LOAD_KEY) from TABLE)) = 'Pass'
    AND (SELECT T7_VALIDATION_OUTCOME 
 FROM TABLE 
 WHERE LOAD_KEY = (select MAX(LOAD_KEY) from TABLE)) = 'Pass'
    THEN 0
    ELSE 1
    into RetVal
  
 FROM TABLE;
    

--
if RetVal = 0 then
   dbms_output.put_line('Pass');   -- T4/T7 Reconciled

ELSE
   dbms_output.put_line('Fail');  -- Either T4 or T7 not reconciled

END IF;
--
END;
/
exit ```

Upvotes: 0

Views: 239

Answers (1)

kfinity
kfinity

Reputation: 9091

I think this query is a simpler version of what you want.

select 
    CASE 
    WHEN T4_VALIDATION_OUTCOME = 'Pass'
        AND T7_VALIDATION_OUTCOME = 'Pass'
    THEN 0
    ELSE 1 
    END into RetVal
FROM TABLE
WHERE LOAD_KEY = (select MAX(LOAD_KEY) from TABLE);

I'm assuming that LOAD_KEY is a unique column - otherwise this could select more than one row, which would raise an exception.

Upvotes: 1

Related Questions