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'