Skip to main content
Solved

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

  • February 9, 2021
  • 1 reply
  • 1695 views

Forum|alt.badge.img+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.

 

 

1select
2ip.contract,
3ip.part_no,
4ip.type_code_db,
5ip.part_product_code as Product_Code,
6ip.type_designation,
7sp.catalog_group as Sales_Group,
8ipp.safety_stock,
9ipcp.qty_available as Available_Inventory,
10ipp.safety_stock - ipcp.qty_available as Over_Short,
11
12
13( SELECT
14osd.qty_supply as Next_Supply_Qty
15from ORDER_SUPP_DEM_EXT_TMP_VIEW osd
16 where osd.contract = ip.contract and osd.part_no = ip.part_no
17 and ( osd.order_supply_demand_type = 'Shop Order' and osd.status_Desc in ('Planned', 'Started', 'Released')
18or osd.order_supply_demand_type = 'Purch Order' and osd.status_Desc in ('Planned', 'Released', 'Received') )
19 order by osd.date_required
20FETCH FIRST 1 ROWS ONLY ) as Next_Supply_Qty,
21
22( SELECT
23osd.date_required as Next_Supply_Date
24from ORDER_SUPP_DEM_EXT_TMP_VIEW osd
25 where osd.contract = ip.contract and osd.part_no = ip.part_no
26 and ( osd.order_supply_demand_type = 'Shop Order' and osd.status_Desc in ('Planned', 'Started', 'Released')
27or osd.order_supply_demand_type = 'Purch Order' and osd.status_Desc in ('Planned', 'Released', 'Received') )
28 order by osd.date_required
29FETCH FIRST 1 ROWS ONLY ) as Next_Supply_Date
30
31from INVENTORY_PART ip
32
33left join SALES_PART sp
34 on ip.contract = sp.contract and ip.part_no = sp.part_no
35
36left join INVENTORY_PART_PLANNING ipp
37 on ip.contract = ipp.contract and ip.part_no = ipp.part_no
38
39left join INV_PART_CONFIG_PROJECT_ALT ipcp
40 on ip.contract = ipcp.contract and ip.part_no = ipcp.part_no
41
42where ip.contract = 'W'
43and ip.part_no LIKE 'SA-%'
44
45order by ip.contract, ip.part_no
46

 

Best answer by DevBob

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:

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

 

 

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

1 reply

Forum|alt.badge.img+10
  • Author
  • Sidekick (Customer)
  • 119 replies
  • Answer
  • February 10, 2021

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:

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

 

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings