SQL: Using the WITH clause

We were wondering if the WITH clause works in IFS. I tried one using Oracle SQL Developer and it hangs up when I run it; it doesn’t return any results. We haven’t put it in a Quick Report yet.

We’re reading InventoryPartsPerPeriod and trying to retrieve recent PO Parts until the ReceiptQty matches on OnHandQty.

Could it be because the tables in the WITH clause are too large?

WITH total_invoices ( contract, part_no, stat_period_no, quantity, i_total ) AS (
  SELECT ivpse.contract, ivpse.part_no, stat_period_no, ivpse.quantity,
         SUM( quantity ) OVER ( ORDER BY stat_year_no, stat_period_no, contract, part_no )
  FROM   REGA1APP.INVENTORY_VALUE_PART_SUM_EXT  ivpse
),

total_payments ( p_contract, p_part_no, planned_received_date, buy_qty_due, p_total ) AS (
  SELECT poi.order_no, poi.part_no, planned_receipt_date, poi.buy_qty_due,
         SUM( buy_qty_due ) OVER ( ORDER BY poi.planned_receipt_date, contract, part_no )
  FROM   REGA1APP.PURCHASE_ORDER_LINE_PART  poi
)


SELECT 
contract,
part_no,

       LEAST( p_total, i_total )
         - GREATEST( p_total - poi.buy_qty_due, i_total - ivpse.quantity ) AS used_pay_amount,
       GREATEST( i_total - p_total, 0 ) AS open_inv_amount

FROM   total_invoices  ivpse
       INNER JOIN
       total_payments   poi
	ON ( i_total - quantity < p_total
           AND i_total > p_total - buy_qty_due )


where quantity <> 0
and STAT_YEAR_NO = '2020'
and STAT_PERIOD_NO = '9'
and contract = 'W'

I frequently use the WITH clause. It works well when you know you want to force materializing a smaller result set before joining it with a larger one. If you don’t pre-filter your results, it will result in a slower execution plan.

The WITH clause doesn’t look like valid SQL in a Quick Report, so you have to wrap it with a SELECT.

SELECT * FROM (
WITH my_with AS (SELECT /*+MATERIALIZE*/ 'Hello World' AS msg FROM DUAL)
SELECT msg FROM my_with
)

(There is no such limitation when you’re creating an IAL.)

WITH total_invoices ( contract, part_no, stat_period_no, quantity, i_total ) AS (
  SELECT ivpse.contract, ivpse.part_no, stat_period_no, ivpse.quantity,
         SUM( quantity ) OVER ( ORDER BY stat_year_no, stat_period_no, contract, part_no )
  FROM   REGA1APP.INVENTORY_VALUE_PART_SUM_EXT  ivpse
),

total_payments ( p_contract, p_part_no, planned_received_date, buy_qty_due, p_total ) AS (
  SELECT poi.order_no, poi.part_no, planned_receipt_date, poi.buy_qty_due,
         SUM( buy_qty_due ) OVER ( ORDER BY poi.planned_receipt_date, contract, part_no )
  FROM   REGA1APP.PURCHASE_ORDER_LINE_PART  poi
)


SELECT 
contract,
part_no,

       LEAST( p_total, i_total )
         - GREATEST( p_total - poi.buy_qty_due, i_total - ivpse.quantity ) AS used_pay_amount,
       GREATEST( i_total - p_total, 0 ) AS open_inv_amount

FROM   total_invoices  ivpse
       INNER JOIN
       total_payments   poi
	ON ( i_total - quantity < p_total
           AND i_total > p_total - buy_qty_due )


where quantity <> 0
and STAT_YEAR_NO = '2020'
and STAT_PERIOD_NO = '9'
and contract = 'W'

What if you add the stat_year_no and stat_period_no as columns?

WITH total_invoices ( contract, part_no, stat_year_no,stat_period_no, quantity, i_total ) AS (
  SELECT ivpse.contract, ivpse.part_no, ivpse.stat_year_no, ivpse.stat_period_no, ivpse.quantity,
         SUM( quantity ) OVER ( ORDER BY stat_year_no, stat_period_no, contract, part_no )
  FROM   IFSAPP.INVENTORY_VALUE_PART_SUM_EXT  ivpse
),

total_payments ( p_contract, p_part_no, planned_received_date, buy_qty_due, p_total ) AS (
  SELECT poi.order_no, poi.part_no, planned_receipt_date, poi.buy_qty_due,
         SUM( buy_qty_due ) OVER ( ORDER BY poi.planned_receipt_date, contract, part_no )
  FROM   IFSAPP.PURCHASE_ORDER_LINE_PART  poi
)


SELECT 
contract,
part_no,
stat_year_no,
stat_period_no,

       LEAST( p_total, i_total )
         - GREATEST( p_total - poi.buy_qty_due, i_total - ivpse.quantity ) AS used_pay_amount,
       GREATEST( i_total - p_total, 0 ) AS open_inv_amount

FROM   total_invoices  ivpse
       INNER JOIN
       total_payments   poi
  ON ( i_total - quantity < p_total
           AND i_total > p_total - buy_qty_due )


where quantity <> 0
and STAT_YEAR_NO = '2020'
and STAT_PERIOD_NO = '9'
and contract = 'W'

You may want to look at the GROUP BY clause. This should be your first choice before using analytical window functions.

SELECT * FROM (
WITH my_with AS (SELECT /*+MATERIALIZE*/ 'Hello World' AS msg FROM DUAL)
SELECT msg FROM my_with
)

Which IFS version you have @durette, as I remember WITH clause is supported in IEE from App9 onwards

Can you describe more detailed in words exactly what you want this query to show? Not technically but the requirement. Then it is easier to say if you are on the right way or if you need to do it differently.

The current SQL does no join on contract and part no, that is one of the things I see right now.