Hello ,
would such an sql statement work please ? Can I divide within a nested case statements ? I don't have access to the database right now and have a deadline to meet , so working at home .
SELECT '(PCS)',nvl(fancy,0)+nvl(polo,0)+nvl(ts,0) tot,nvl(fancy,0) fancy,nvl(polo,0) polo,nvl(ts,0) ts
FROM
(
select sty_prod_group,sum((CASE WHEN fetch_count_colours (x.oco_id) = 1 THEN
(case when get_prior_route(a.sty_id,'SEW')='EMB' then
NVL(get_colsiz_achvd ('EMBRO',
x.oco_id,
x.osz_id),0) - qty_load
when get_prior_route(a.sty_id,'SEW')='PRT' then
NVL(get_colsiz_achvd ('PRINT',
x.oco_id,
x.osz_id),0) -qty_load
else
qty_cut - qty_load
end)
WHEN fetch_count_colours (x.oco_id) > 1 THEN
(case when sct_proc.get_prior_route(a.sty_id,'SEW')='EMB' then
NVL(get_colsiz_achvd ('EMBRO',
x.oco_id,
x.osz_id),0) - qty_load
when get_prior_route(a.sty_id,'SEW')='PRT' then
NVL(get_colsiz_achvd ('PRINT',
x.oco_id,
x.osz_id),0) -qty_load
else
qty_cut - qty_load
end) / fetch_count_colours (x.oco_id)
END))tmp
from orders_style_v a, ordcolsize_progress_v x
where a.ord_id = x.ord_id
GROUP BY sty_prod_group
)
PIVOT
(
SUM(tmp)
FOR sty_prod_group IN ('FANCY' AS "FANCY", 'POLO' AS "POLO", 'TS' AS "TS")
)