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!
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.