Oracle Functions – Does Select Clause Function Evaluate Columns Before Where Clause?

cursorsfunctionsoracle

I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question.

            select submission_identifier,
                   submitted_by_person_id,
                   submission_date,
                   form_version_date_time,
                   form_id,
                   func_get_open_days_2(
                     submitted_by_person_id, 
                     submission_date, 
                     form_version_date_time, 
                     form_id)` open_days
            from form_submission
            where submission_identifier = 2070;

submission_identifier is the PK for the form_submission table, so naturally, this query returns one result.

As you can see, the function takes 4 parameters. I'm assuming the values in those parameters are the results of the query AFTER the where clause has been evaluated. Here is a snippet of the function.

create or replace function        func_get_Open_Days_2
   ( SUBMITTED_BY_PERSON_ID in number,
  SUBMISSION_DATE in date,
  FORM_VERSION_DATE_TIME in timestamp,
  FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT 
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = SUBMITTED_BY_PERSON_ID and
        t.form_id = FORM_ID and
        t.submission_date = SUBMISSION_DATE and
        t.form_version_date_time = FORM_VERSION_DATE_TIME and 
        t.submission_status_code NOT IN (3469, 3462);
        order by t.status_date asc;

   rec records%ROWTYPE;

BEGIN

dbms_output.put_line('submitted by person id ='||submitted_by_person_id);
dbms_output.put_line('form id ='||form_id);
dbms_output.put_line('submission date ='||submission_date);
dbms_output.put_line('form_version_date_time ='||form_version_date_time);

  -- go through all the durations in audit_trail table
  FOR rec in records
  LOOP

I have tried hard-coding the return values and putting them in this cursor select query. I receive 1 result.

This suggests to me that the "records" array or list should only be 1 result long. However, I am getting numerous results as it stands now. "records" are being returned which suggest the where clause is not being evaluated using the 4 values/results I'd expect I'm not sure what I'm doing wrong. The the "dbms_output.put_line" are the BEGIN but before the LOOP confirm the expected values are reaching the function.

Best Answer

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause):

create or replace function        func_get_Open_Days_2
   ( ARG_SUBMITTED_BY_PERSON_ID in number,
  ARG_SUBMISSION_DATE in date,
  ARG_FORM_VERSION_DATE_TIME in timestamp,
  ARG_FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT 
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = ARG_SUBMITTED_BY_PERSON_ID and
        t.form_id = ARG_FORM_ID and
        t.submission_date = ARG_SUBMISSION_DATE and
        t.form_version_date_time = ARG_FORM_VERSION_DATE_TIME and 
        t.submission_status_code NOT IN (3469, 3462);
      order by t.status_date asc;
Related Question