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