Keep getting errors when compiling functions

It seems to me that there is missing ; after time_in_quarantine at line 26

)  time_in_quarantine;

Thank you Helena but I have tried that and I know receive a new error: Error(20,125): PL/SQL: ORA-00904: "STAMPTIME": invalid identifier along with Error(7,1): PL/SQL: SQL Statement ignored. I have attached a screenshot and the new code with the additional ; after time_in_quarantine.

 

create or replace FUNCTION BA_REC_EXCUSABLE_DELAY_V2(v_rc_number IN NUMBER, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)
RETURN NUMBER
IS v_days NUMBER(11,2);
BEGIN
-- THIS FUNCTION RETURNS THE NUMBER OF DAYS A RC SPENT IN QUARANTINE

select round(sum(case when time_in_quarantine.start_time>nvl(p_before,sysdate) then 0
               else case when time_in_quarantine.stop_time<=nvl(p_before,sysdate) then time_in_quarantine.stop_time-time_in_quarantine.start_time
               else nvl(p_before,sysdate)-time_in_quarantine.start_time end end))
into v_days

from(
               select RC_NUMBER,
                              case when rca.time_stamp>=nvl(v_wo_receiving_date,rca.time_stamp) then 
                                             case when rca.time_stamp<=nvl(v_wo_entry_date,rca.time_stamp) then rca.time_stamp else v_wo_entry_date end
                                             else case when v_wo_receiving_date<=nvl(v_wo_entry_date,rca.time_stamp) then v_wo_receiving_date else v_wo_entry_date end
                              end start_time,
                              (select case when nvl(min(stamptime),sysdate)>=nvl(v_wo_receiving_date,nvl(min(stamptime),sysdate)) then
                                                            case when nvl(min(stamptime),sysdate)<=nvl(v_wo_entry_date,nvl(min(stamptime),sysdate)) then nvl(min(stamptime),sysdate) else v_wo_entry_date end
                                                            else case when v_wo_receiving_date<=nvl(v_wo_entry_date,nvl(min(stamptime),sysdate)) then v_wo_receiving_date else v_wo_entry_date end
                                                            end
                                             from rc_audit_log
                                             where rc_number = rca.rc_number and disp_code='OK' and time_stamp>rca.time_stamp) stop_time
               from rc_audit_log rca
               where rca.rc_number = v_rc_number and rca.disp_code = 'QUARANTINE'
) time_in_quarantine;

   RETURN(v_days);
END BA_REC_EXCUSABLE_DELAY_V2;

Select allOpen in new window

Capture.PNG

You deleted your other question before I could post.

Are you sure you need all the case statements and sub select?  It looks a little overly complex and you are hitting the rc_audit_log table twice.

Before you ask, no, I cannot rewrite it for you because I don't have your tables or data to work with.  I'm just going by my gut feeling that with that many nested case statements and a sub-query, it can probably be simplified.

Agreed with both. It is one of those "above my pay grade" decisions. Slightvw, never asked you to rewrite it nor was I planning to.

Helena, The column STAMPTIME has been changed to TIME_STAMP, I missed it and thank you for that. After changing the column to the correct one, I am receiving the following errors below. I ave also embedded the modified SQL. I will appreciate any insight.

create or replace FUNCTION BA_REC_EXCUSABLE_DELAY_V2(v_rc_number IN NUMBER, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)
RETURN NUMBER
IS v_days NUMBER(11,2);
BEGIN
-- THIS FUNCTION RETURNS THE NUMBER OF DAYS A RC SPENT IN QUARANTINE

select round(sum(case when time_in_quarantine.start_time>nvl(p_before,sysdate) then 0
               else case when time_in_quarantine.stop_time<=nvl(p_before,sysdate) then time_in_quarantine.stop_time-time_in_quarantine.start_time
               else nvl(p_before,sysdate)-time_in_quarantine.start_time end end))
into v_days

from(
               select RC_NUMBER,
                              case when rca.time_stamp>=nvl(v_wo_receiving_date,rca.time_stamp) then 
                                             case when rca.time_stamp<=nvl(v_wo_entry_date,rca.time_stamp) then rca.time_stamp else v_wo_entry_date end
                                             else case when v_wo_receiving_date<=nvl(v_wo_entry_date,rca.time_stamp) then v_wo_receiving_date else v_wo_entry_date end
                              end start_time,
                              (select case when nvl(min(time_stamp),sysdate)>=nvl(v_wo_receiving_date,nvl(min(time_stamp),sysdate)) then
                                                            case when nvl(min(time_stamp),sysdate)<=nvl(v_wo_entry_date,nvl(min(time_stamp),sysdate)) then nvl(min(time_stamp),sysdate) else v_wo_entry_date end
                                                            else case when v_wo_receiving_date<=nvl(v_wo_entry_date,nvl(min(time_stamp),sysdate)) then v_wo_receiving_date else v_wo_entry_date end
                                                            end
                                             from rc_audit_log
                                             where rc_number = rca.rc_number and disp_code='OK' and time_stamp>rca.time_stamp) stop_time
               from rc_audit_log rca
               where rca.rc_number = v_rc_number and rca.disp_code = 'QUARANTINE'
) time_in_quarantine;

   RETURN(v_days);
END BA_REC_EXCUSABLE_DELAY_V2;

Select allOpen in new window

Thank you for all your help