Hello,
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