Oracle function where clause not considering all filtering

Question

First of all, I usually try to replicate my problem in some dummy data to make it easier to ask here but this at time it won't be possible. So please forgive me for this.

So. My problem is the following. I have a Oracle Database and on it I have seven tables on which I want to perform a select doing LEFT JOIN between then. So. This is the raw script

select turn_on_off.ID as Event_ID,controllers.ID as Ctrl_ID,Ctrl,SubCtrl,Turn_OFF,Turn_ON,DiscountPCT,Reason,Observation,Comments
from
sagi_un
left join
sagi_industrial
on
sagi_un.ID = sagi_industrial.UN_ID
left join
sagi_plant
on
sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
left join
sagi_area
on
sagi_plant.ID = sagi_area.PLANT_ID
left join
sagi_area_ctrl_map
on
sagi_area.ID = sagi_area_ctrl_map.AREA_ID
left join
controllers
on
controllers.ID = sagi_area_ctrl_map.CTRL_ID
left join
turn_on_off
on
sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
left join
justification
on
turn_on_off.ID = justification.Event_ID
left join
reasons
on
reasons.ID = justification.REASON_ID
where
Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY') and
sagi_un.UN = 'Q 2 RS' and
sagi_industrial.Industrial = 'Olefinas' and
sagi_plant.plant = 'OLE-2' and
sagi_area.area = 'Area Quente'

at the end of the query I have a where clause where I set up some filtering for my data. This works fine. To make our live easier when accessing this data I created a pipelined function which receive the filtering parameter and should return the query result. This is the code that generate the function

create or replace FUNCTION                  "GET_OFF_INTERVALS2" 
(
  UN IN VARCHAR2 
, INDUSTRIAL IN VARCHAR2 
, PLANTA IN VARCHAR2 
, AREA IN VARCHAR2 
, MAX_DATE IN DATE 
) RETURN OFF_INTERVAL_TABLE PIPELINED IS
type ref0 is ref cursor;
cur0 ref0;
out_rec OFF_INTERVAL
:= OFF_INTERVAL(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
open cur0 for
select turn_on_off.ID as Event_ID,controllers.ID as Ctrl_ID,Ctrl,SubCtrl,Turn_OFF,Turn_ON,DiscountPCT,Reason,Observation,Comments
from
sagi_un
left join
sagi_industrial
on
sagi_un.ID = sagi_industrial.UN_ID
left join
sagi_plant
on
sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
left join
sagi_area
on
sagi_plant.ID = sagi_area.PLANT_ID
left join
sagi_area_ctrl_map
on
sagi_area.ID = sagi_area_ctrl_map.AREA_ID
left join
controllers
on
controllers.ID = sagi_area_ctrl_map.CTRL_ID
left join
turn_on_off
on
sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
left join
justification
on
turn_on_off.ID = justification.Event_ID
left join
reasons
on
reasons.ID = justification.REASON_ID
where
Turn_OFF > max_date and
sagi_un.UN = UN and
sagi_industrial.Industrial = INDUSTRIAL and
sagi_plant.plant = PLANTA and
sagi_area.area = AREA;

  loop
    fetch cur0 into 
      out_rec.event_id,
      out_rec.ctlr_id,
      out_rec.ctrl,
      out_rec.subctrl,
      out_rec.turn_on,
      out_rec.turn_off,
      out_rec.discount_pct,
      out_rec.reason,
      out_rec.observation,
      out_rec.comments;
    exit when cur0%NOTFOUND;
    pipe row(out_rec);
  end loop;
close cur0;
  RETURN;
END GET_OFF_INTERVALS2;

and I call this function like this.

select * from TABLE(GET_OFF_INTERVALS2('Q 2 RS','Olefinas','OLE-2','Area Quente',TO_DATE('10-07-2022','DD-MM-YYYY')));

You can see that the filtering parameters are the same.

The problem is that the function seems to be not considering the AREA parameter. In the data I am testing there are two areas (Area Quente and Area Fria) but the pipeline function is returning the data for both areas instead of the area I am requesting. It is like that line was commented. I think that I am doing something really stupid here but I am not able to see what it is. If someone can take a look on it an point me out my mistake I would be very gratefull. thanks!

score 2 · Accepted Answer · answered Jul 11 '22 at 17:33

Your filter conditions turn the LEFT [OUTER] JOIN into an INNER JOIN as the WHERE condition requires that there be a matched row and cannot match a NULL row.

select turn_on_off.ID as Event_ID,
       controllers.ID as Ctrl_ID,
       Ctrl,
       SubCtrl,
       Turn_OFF,
       Turn_ON,
       DiscountPCT,
       Reason,
       Observation,
       Comments
from   sagi_un
       INNER JOIN sagi_industrial
       on sagi_un.ID = sagi_industrial.UN_ID
       INNER JOIN sagi_plant
       on sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
       INNER JOIN sagi_area
       on sagi_plant.ID = sagi_area.PLANT_ID
       INNER JOIN sagi_area_ctrl_map
       on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
       INNER JOIN turn_on_off
       on sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
       left join controllers
       on controllers.ID = sagi_area_ctrl_map.CTRL_ID
       left join justification
       on turn_on_off.ID = justification.Event_ID
       left join reasons
       on reasons.ID = justification.REASON_ID
where  Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY')
and    sagi_un.UN                 = 'Q 2 RS'
and    sagi_industrial.Industrial = 'Olefinas'
and    sagi_plant.plant           = 'OLE-2'
and    sagi_area.area             = 'Area Quente'

If you want to LEFT OUTER JOIN then you need to include the filter in the ON condition of the JOIN:

select turn_on_off.ID as Event_ID,
       controllers.ID as Ctrl_ID,
       Ctrl,
       SubCtrl,
       Turn_OFF,
       Turn_ON,
       DiscountPCT,
       Reason,
       Observation,
       Comments
from   sagi_un
       LEFT OUTER JOIN sagi_industrial
       on (   sagi_un.ID = sagi_industrial.UN_ID
          AND sagi_industrial.Industrial = 'Olefinas')
       LEFT OUTER JOIN sagi_plant
       on (   sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
          AND sagi_plant.plant = 'OLE-2' )
       LEFT OUTER JOIN sagi_area
       on (   sagi_plant.ID = sagi_area.PLANT_ID
          AND sagi_area.area = 'Area Quente' )
       LEFT OUTER JOIN sagi_area_ctrl_map
       on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
       LEFT OUTER JOIN controllers
       on controllers.ID = sagi_area_ctrl_map.CTRL_ID
       LEFT OUTER JOIN turn_on_off
       on ( sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
          AND Turn_OFF > TO_DATE('10-07-2022','DD-MM-YYYY') )
       LEFT OUTER JOIN justification
       on turn_on_off.ID = justification.Event_ID
       LEFT OUTER JOIN reasons
       on reasons.ID = justification.REASON_ID
where  sagi_un.UN = 'Q 2 RS'

As for your function, NEVER give PL/SQL variables the same name as an SQL column (if you do then the local SQL scope will be considered before the outer PL/SQL scope so you are effectively using WHERE sagi_un.UN = sagi_un.UN in the comparisons and not WHERE sagi_un.UN = plsql_UN):

CREATE FUNCTION GET_OFF_INTERVALS2
(
  v_UN         IN sagi_un.UN%TYPE
, v_INDUSTRIAL IN sagi_industrial.Industrial%TYPE
, v_PLANT      IN sagi_plant.plant%TYPE
, v_AREA       IN sagi_area.area%TYPE
, v_MAX_DATE   IN turn_on_off.Turn_OFF%TYPE
) RETURN OFF_INTERVAL_TABLE PIPELINED
IS
  cur0 REF CURSOR;
  out_rec OFF_INTERVAL
:= OFF_INTERVAL(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
  open cur0 for
  select turn_on_off.ID as Event_ID,
         controllers.ID as Ctrl_ID,
         Ctrl,
         SubCtrl,
         Turn_OFF,
         Turn_ON,
         DiscountPCT,
         Reason,
         Observation,
         Comments
  from   sagi_un
         LEFT OUTER JOIN sagi_industrial
         on (   sagi_un.ID = sagi_industrial.UN_ID
            AND sagi_industrial.Industrial = v_industrial)
         LEFT OUTER JOIN sagi_plant
         on (   sagi_industrial.ID = sagi_plant.INDUSTRIAL_ID
            AND sagi_plant.plant = v_plant )
         LEFT OUTER JOIN sagi_area
         on (   sagi_plant.ID = sagi_area.PLANT_ID
            AND sagi_area.area = v_area )
         LEFT OUTER JOIN sagi_area_ctrl_map
         on sagi_area.ID = sagi_area_ctrl_map.AREA_ID
         LEFT OUTER JOIN controllers
         on controllers.ID = sagi_area_ctrl_map.CTRL_ID
         LEFT OUTER JOIN turn_on_off
         on ( sagi_area_ctrl_map.CTRL_ID = turn_on_off.CTRL_ID
            AND Turn_OFF > v_max_date )
         LEFT OUTER JOIN justification
         on turn_on_off.ID = justification.Event_ID
         LEFT OUTER JOIN reasons
         on reasons.ID = justification.REASON_ID
  where  sagi_un.UN = v_un;

  loop
    fetch cur0 into 
      out_rec.event_id,
      out_rec.ctlr_id,
      out_rec.ctrl,
      out_rec.subctrl,
      out_rec.turn_on,
      out_rec.turn_off,
      out_rec.discount_pct,
      out_rec.reason,
      out_rec.observation,
      out_rec.comments;
    exit when cur0%NOTFOUND;
    pipe row(out_rec);
  end loop;
  close cur0;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    close cur0;
END GET_OFF_INTERVALS2;
/

Note: You should also catch the NO_DATE_NEEDED exception so you can close the cursor if the function is stopped calling before all the rows are read.

Thank you very much MT0. This make it work. I wasn't aware that it was possible to use the filter in the ON clause. — Dariva, Jul 11 '22 at 17:49

Oracle function where clause not considering all filtering

1 Answers1