Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function call within SQL case statement

DooliusJun 24 2014 — edited Jun 24 2014

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

This post has been answered by John Spencer on Jun 24 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2014
Added on Jun 24 2014
2 comments
971 views