Skip to main content

This quick report’s subquery that has FETCH FIRST 1 ROWS ONLY to pull the first PO is only returning one row for the entire report; not for each part#:

SA-807 has a qualifying row as well; we should see a Next Supply Qty and Next Supply Date as we ll as SA-811.

 

 

select
ip.contract,
ip.part_no,
ip.type_code_db,
ip.part_product_code as Product_Code,
ip.type_designation,
sp.catalog_group as Sales_Group,
ipp.safety_stock,
ipcp.qty_available as Available_Inventory,
ipp.safety_stock - ipcp.qty_available as Over_Short,


( SELECT
osd.qty_supply as Next_Supply_Qty
from ORDER_SUPP_DEM_EXT_TMP_VIEW osd
  where osd.contract = ip.contract  and osd.part_no = ip.part_no 
  and ( osd.order_supply_demand_type = 'Shop Order' and osd.status_Desc in ('Planned', 'Started', 'Released')
or osd.order_supply_demand_type = 'Purch Order' and osd.status_Desc in ('Planned', 'Released', 'Received') )
 order by  osd.date_required
FETCH FIRST 1 ROWS ONLY ) as Next_Supply_Qty,

( SELECT
osd.date_required as Next_Supply_Date
from ORDER_SUPP_DEM_EXT_TMP_VIEW osd
  where osd.contract = ip.contract  and osd.part_no = ip.part_no 
  and ( osd.order_supply_demand_type = 'Shop Order' and osd.status_Desc in ('Planned', 'Started', 'Released')
or osd.order_supply_demand_type = 'Purch Order' and osd.status_Desc in ('Planned', 'Released', 'Received') )
 order by  osd.date_required
FETCH FIRST 1 ROWS ONLY ) as Next_Supply_Date

from INVENTORY_PART ip

left join SALES_PART  sp
  on ip.contract = sp.contract and ip.part_no = sp.part_no

left join INVENTORY_PART_PLANNING   ipp
 on ip.contract = ipp.contract and ip.part_no = ipp.part_no

left join INV_PART_CONFIG_PROJECT_ALT ipcp
  on ip.contract = ipcp.contract and ip.part_no = ipcp.part_no

where ip.contract = 'W'
and ip.part_no LIKE 'SA-%'

order by ip.contract, ip.part_no

 

I figured it out- I was using as temp IFS table view that is populated from this screen, so it only contains 1 part’s data:

 

incorrect table: ORDER_SUPP_DEM_EXT_TMP_VIEW

correct table: ORDER_SUPPLY_DEMAND

 

corrected SQL:

( SELECT
osd.qty_supply as Next_Supply_Qty
from ORDER_SUPPLY_DEMAND osd
  where osd.contract = ip.contract  and osd.part_no = ip.part_no 
  and ( osd.order_supply_demand_type = 'Shop Order' and osd.description in ('Planned', 'Started', 'Released')
or osd.order_supply_demand_type = 'Purch Order' and osd.description in ('Planned', 'Released', 'Received') )
 order by  osd.date_required
FETCH FIRST 1 ROWS ONLY ) as Next_Supply_Qty,

 

 

Terms & ConditionsCookie settings