Hello, (Also created in Framework and Experience)
I'm struggling with making my report work to use a dynamic search filter under Pivot for part_no. Does anyone have experience with this? Note, I don't use pivot in SQL very often and tried different approaches.
I tried creating a subquery for shop_ord without success. I've also tried using regex:
part_no IN
(SELECT trim(regexp_substr('&part_no','[^;]+', 1, level)) AS q
FROM dual
CONNECT BY regexp_substr('&part_no', '[^;]+', 1, level) IS NOT NULL
)
I also tried to use query flags without any success. So, I'm a bit stuck and unsure whether this can be done. Does anyone have experience with this?
My SQL:
WITH weeks AS (
SELECT TO_CHAR(TRUNC(SYSDATE, 'D') + (8 - TO_CHAR(SYSDATE, 'D')), 'IW') + (LEVEL - 1) AS week_number
FROM DUAL
CONNECT BY LEVEL <= 21
),
data AS (
SELECT
TO_CHAR(TRUNC(s.REVISED_DUE_DATE, 'IW'), 'IW') AS week_number,
s.PART_NO,
SUM(NVL(s.REMAINING_NET_SUPPLY_QTY, 0)) AS total_qty
FROM SHOP_ORD s
WHERE
s.STATE IN ('Planned', 'Released', 'Reserved', 'Started')
AND s.REVISED_DUE_DATE >= TRUNC(SYSDATE, 'D') + (8 - TO_CHAR(SYSDATE, 'D'))
AND s.REVISED_DUE_DATE < TRUNC(SYSDATE, 'D') + (8 - TO_CHAR(SYSDATE, 'D')) + (21 * 7)
GROUP BY TO_CHAR(TRUNC(s.REVISED_DUE_DATE, 'IW'), 'IW'), s.PART_NO
)
SELECT *
FROM (
SELECT
w.week_number,
d.PART_NO,
NVL(d.total_qty, 0) AS total_qty
FROM weeks w
LEFT JOIN data d
ON w.week_number = d.week_number
)
PIVOT (
SUM(total_qty)
FOR PART_NO IN (
'205591', '205602', '205606'
)
)
ORDER BY week_number
Thanks!!
Cheers