i want to use a dynamic expression in PIVOT values

31 views Asked by At

currently, i am unable to use a query due to a non-constant expression used in pivot values.

i need help to fix this.

error message received: non-constant expression is not allowed for pivot|unpivot values

query used:

SELECT
    *
FROM
    (
        SELECT
            gt.tp_short_name                     AS store,
            to_char(oh.created_date, 'MON-YYYY') AS month_val,
            oh.order_ref_no                      AS order_id
        FROM
            order_hdr    oh,
            gnrl_tp      gt,
            gnrl_tp_type gtt
        WHERE
                oh.customer_id = gt.tp_id
            AND gt.tp_id = gtt.tp_id
            AND trunc(oh.created_date) >= ?
            AND trunc(oh.created_date) < ?
            AND oh.service_type IN ( ? )
            AND gtt.tp_type = 'TPTYPE-CUSTOMER'
            AND gt.active_ind = 1
            AND gt.inact_date IS NULL
            AND oh.inact_date IS NULL
            AND gt.tp_short_name IN (
                SELECT
                    org_short_name
                FROM
                    gnrl_org
                WHERE
                    org_id IN ( ?, ? )
            )
        GROUP BY
            gt.tp_short_name,
            to_char(oh.created_date, 'MON-YYYY'),
            oh.order_ref_no
        ORDER BY
            gt.tp_short_name,
            to_char(oh.created_date, 'MON-YYYY')
    ) PIVOT (
        COUNT(order_id)
        FOR month_val
        IN **( 'to_char(oh.created_date, 'MON-YYYY')' )**      --> i need **SEP-2023** TO COME HERE 
    )
ORDER BY
    store ASC
0

There are 0 answers