troubleshooting Question

Oracle nested select

Avatar of Vinum
VinumFlag for Denmark asked on
Oracle Database
5 Comments1 Solution357 ViewsLast Modified:
I have the below SQL for Oracle.

I have added a select with
-- NEW CODE FROM HERE nad -- NEW CODE TO HERE

But I get an error: ORA-00904: "Z"."PART_NO": invalid identifier
on line marked with -- ERROR HERE

Why can't I reference to Z ?
select
z.part_no,
z.description,
--z.JF_onhand,
--z.JFP_Onhand,
z.maxlager,
--z.JFP_Demand54_Today as Demand54,
z.HentFraJ as Hent_Fra_J,
z.HentFraNotJ as Hent_Fra_Ikke_J,
(select ipl.location_no
from KK.INVENTORY_PART_DEF_LOC ipl
where ipl.contract = 'JF' and
      ipl.part_no = z.part_no and 
      ipl.location_type = 'Picking') as TilLokation,
(select
wm_concat(ips.location_no||'='||(ips.qty_onhand - ips.qty_reserved)||'  ')
from
KK.INVENTORY_PART_IN_STOCK ips 
where ips.contract = 'JFP' and ips.part_no = z.part_no and (ips.qty_onhand - ips.qty_reserved) > 0 and substr(ips.location_no,1,1) in ('G','H','J','S','C','D','M') 
) as Lokationer,
'_______________' as HENTET,

-- NEW CODE FROM HERE
(select
--i2.location_no
i2.part_no
from 
(select
i1.contract,
i1.part_no,
i1.location_no,
i1.qty_onhand - i1.qty_reserved as freeqty,
case substr(i1.location_no,1,1)        
 when 'J' then '1'
 when 'M' then '2'
 when 'S' then '3'
 when 'G' then '4'   
 else '9'  
end as sortering
from KK.INVENTORY_PART_IN_STOCK i1 
        where i1.contract = 'JFP' and 
              i1.part_no = z.part_no and -- ERROR HERE
              substr(i1.location_no,1,1) in ('J','G','H','S','C','D','M') and
              (i1.qty_onhand - i1.qty_reserved) > 0
order by i1.part_no, sortering) i2
where rownum = 1) as fralokation 
-- NEW CODE TO HERE


from

(select
y.part_no,
y.description,
--y.location_no,
y.JF_onhand,
y.JFP_Onhand,
y.JFP_Onhand_J,
y.JFP_Onhand_Not_J,
y.MaxLager,
y.JF_Demand_Today,
y.JFP_Demand_Today,
y.JFP_Demand54_Today,
y.DerErPladsTil,
y.Hent,
least(y.JFP_Onhand_j,Hent) as HentFraJ,
least(y.JFP_Onhand_Not_J,Hent - least(y.JFP_Onhand_j,Hent) ) as HentFraNotJ

from

(select
x.contract,
x.part_no,
x.description,
x.location_no,
x.JF_onhand,
x.JFP_Onhand,
x.JFP_Onhand_J,
x.JFP_Onhand_Not_J,
x.MaxLager,
x.JF_Demand_Today,
x.JFP_Demand_Today,
x.JFP_Demand54_Today,

greatest(0,(x.MaxLager - x.JF_onhand)) as DerErPladsTil,
least (x.JFP_Demand54_Today,greatest(0,(x.MaxLager - x.JF_onhand))) as Hent

from

(select
ip.contract,
ip.part_no,
ip.description,
ipl.location_no,
greatest (0,nvl((select sum(ips.qty_onhand) 
        from KK.INVENTORY_PART_IN_STOCK ips 
        where ips.contract = 'JF' and ips.part_no = ip.part_no and ips.location_no = ipl.location_no),0)) as JF_Onhand,

greatest (0,nvl((select sum(ips.qty_onhand - ips.qty_reserved) 
        from KK.INVENTORY_PART_IN_STOCK ips 
        where ips.contract = 'JFP' and ips.part_no = ip.part_no),0)) as JFP_Onhand,
greatest (0,nvl((select sum(ips.qty_onhand - ips.qty_reserved)
        from KK.INVENTORY_PART_IN_STOCK ips 
        where ips.contract = 'JFP' and ips.part_no = ip.part_no and substr(ips.location_no,1,1) = 'J'),0)) as JFP_Onhand_J,
greatest (0,nvl((select sum(ips.qty_onhand - ips.qty_reserved) 
        from KK.INVENTORY_PART_IN_STOCK ips 
        where ips.contract = 'JFP' and ips.part_no = ip.part_no and substr(ips.location_no,1,1) in ('G','H','S','C','D','M')),0)) as JFP_Onhand_Not_J,
        
nvl((select kpc.attr_value_numeric 
        from KK.INVENTORY_PART_CHAR_ALL kpc 
        where kpc.contract = 'JF' and kpc.part_no = ip.part_no and kpc.characteristic_code = 'RES-M'),0) as MaxLager,
        
nvl((select sum(osd.qty_demand)
        from KK.ORDER_SUPPLY_DEMAND_EXT osd        
        where osd.contract = 'JF' and osd.part_no = ip.part_no
        and osd.date_required <= sysdate
        ),0) as JF_Demand_Today,

nvl((select sum(osd.qty_demand)
        from KK.ORDER_SUPPLY_DEMAND_EXT osd        
        where osd.contract = 'JFP' and osd.part_no = ip.part_no and osd.order_no not in ('150','152')
        and osd.date_required <= sysdate  
        ),0) as JFP_Demand_Today,

nvl((select sum(osd.qty_demand)
        from KK.ORDER_SUPPLY_DEMAND_EXT osd        
        where osd.contract = 'JFP' and osd.part_no = ip.part_no and osd.order_no in ('150','152')
        and osd.date_required <= sysdate  
        ),0) as JFP_Demand54_Today

from kk.inventory_part ip
left join KK.INVENTORY_PART_DEF_LOC ipl
     on ipl.contract = ip.contract and
        ipl.part_no = ip.part_no and 
        ipl.location_type = 'Picking'
where ip.contract = 'JF'
and ipl.location_no not in ('P','R') and substr(ipl.location_no,1,1) in ('P','R')) x
where x.MaxLager > 0
) y
where (least(y.JFP_Onhand_j,Hent) > 0 or least(y.JFP_Onhand_Not_J,Hent - least(y.JFP_Onhand_j,Hent) ) > 0))z
where rownum <= 100
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros