Solved

SQL- SubQuery's FETCH FIRST 1 ROWS ONLY operating on entire results instead of each row

  • 9 February 2021
  • 1 reply
  • 958 views

Userlevel 4
Badge +10
  • Sidekick (Customer)
  • 119 replies

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

 

icon

Best answer by DevBob 10 February 2021, 03:25

View original

This topic has been closed for comments

1 reply

Userlevel 4
Badge +10

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,