I have a procedure with many case statements in it that compare data within a table and populate an error description of the dates don't validate. (small code snip below)
create or replace procedure "PROC_TEMP_UPDATE"
is
begin
update temp_update
set error_desc =
CASE
WHEN (error_desc != 'MATCH')
THEN 'PCFN/TCN combo not found in destination table.'
WHEN (error_desc = 'SKIP')
THEN NULL
WHEN (ge_ata is null and az_ata is not null)
THEN 'Enter GE ATA Date before entering AZ ATA Date.'
WHEN ((ge_ata is not null and az_ata is not null) and ge_ata > az_ata)
THEN 'GE ATA Date cannot be greater than AZ ATA Date.'
end proc_temp_update;
Instead of having to enter the bottom 2 WHEN statements for every date I created these functions:
(Function 1: check if Date1 is null and Date2 is not.
Function 2:if both dates are not null, check to see if Date1 is Greater than Date2.)
function Is_D1_NULL
(Date1 DATE, Date2 DATE)
return BOOLEAN is
result BOOLEAN := FALSE;
begin
if (Date1 is null and Date2 is not null) then
result := TRUE;
end if;
return (result);
end Is_D1_NULL;
function D1_Greater_D2
(Date1 DATE
Date2 DATE)
return BOOLEAN is
result BOOLEAN := FALSE;
begin
if ((Date1 is not null and Date2 is not null) and Date1 > Date2) then
result := TRUE;
end if;
return (result);
end D1_Greater_D2;
But when I change the WHEN statements to:
WHEN Is_D1_NULL(ge_ata,az_ata)
THEN 'Enter GE ATA Date before entering AZ ATA Date.'
AND
WHEN D1_Greater_D2(ge_ata,az_ata)
THEN 'GE ATA Date cannot be greater than AZ ATA Date.'
The procedure doesn't compile anymore.
Is there something wrong with the code? or is this not possible?
Thanks
Steven