SQL: Using the WITH clause

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'

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

Hmm, I wasn’t aware this could be done with GROUP BY? Could you provide a simple example?

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.