Toad World® Forums

CASE expression

Hi

sorry newbie, and having a little trouble refining a case expression

back ground want to total FC’s of all of field USERSTING_6 but for SS units just want K items totalled

come up with this but getting: missing right parenthesis

cheers Dan

select

level2, sum(NF1 * COST), sum(NF2 * COST), sum(NF3 * COST), sum(NF4 * COST), sum(NF5 * COST), sum(NF6 * COST), SUM(SSTOCK * COST)

from(select pr.userstring_2 as level2, nf.period_1 as NF1, nf.period_2 AS NF2, nf.period_3 AS NF3, nf.period_4 AS NF4, nf.period_5 AS NF5, nf.period_6 AS NF6, cp.c_price as cost

FROM

CHAN_PRODS CP,MONTHLY_HIST MH, NET_FCAST NF, PRODUCTS PR

CASE

WHEN CP.USERSTRING_6 = ‘Q’ THEN 0

WHEN CP.USERSTRING_6 = ‘A’ THEN 0

WHEN CP.USERSTRING_6 = ‘B’ THEN 0

WHEN CP.USERSTRING_6 = ‘C’ THEN 0

WHEN CP.USERSTRING_6 = ‘H’ THEN 0

WHEN CP.USERSTRING_6 = ‘K’ THEN CP.SS_UNITS

END AS SSTOCK

WHERE ((CP.PROD_CD = NF.PROD_CD AND CP.CHAN_ID = NF.CH