Thread: Using CASE in SUM column View Single Post

 tnzabo Registered User Join Date: Feb 2005 Location: , , . Posts: 5 Thanks: 0 Thanked 0 Times in 0 Posts I've found a way to solve this. Here is the code that worked...

CREATE OR REPLACE VIEW FRT_SALES AS
SELECT CUSTOMER,
PROJECT,
SUM(CASE WHEN P.Month = 1 THEN P.Amount ELSE 0 END) AS OCT,
SUM(CASE WHEN P.Month = 2 THEN P.Amount ELSE 0 END) AS NOV,
SUM(CASE WHEN P.Month = 3 THEN P.Amount ELSE 0 END) AS DEC,
SUM(CASE WHEN P.Month = 4 THEN P.Amount ELSE 0 END) AS JAN,
SUM(CASE WHEN P.Month = 5 THEN P.Amount ELSE 0 END) AS FEB,
SUM(CASE WHEN P.Month = 6 THEN P.Amount ELSE 0 END) AS MAR,
SUM(CASE WHEN P.Month = 7 THEN P.Amount ELSE 0 END) AS APR,
SUM(CASE WHEN P.Month = 8 THEN P.Amount ELSE 0 END) AS MAY,
SUM(CASE WHEN P.Month = 9 THEN P.Amount ELSE 0 END) AS JUN,
SUM(CASE WHEN P.Month = 10 THEN P.Amount ELSE 0 END) AS JUL,
SUM(CASE WHEN P.Month = 11 THEN P.Amount ELSE 0 END) AS AUG,
SUM(CASE WHEN P.Month = 12 THEN P.Amount ELSE 0 END) AS SEP
FROM (SELECT CUSTOMER,
PROJECT,
CAST(AMOUNT AS float) AS AMOUNT,
TO_CHAR(INVOICEDATE, 'MM') as MONTH
FROM (SELECT T\$CUNO as CUSTOMER,
T\$CPRJ as PROJECT,
T\$INVD as INVOICEDATE,
T\$INVN as INVOICENUMBER,
T\$AMNT as AMOUNT,
T\$ITEM as ITEM
FROM TTDSLS045400
WHERE T\$ITEM <> 'FRTSALES'
AND T\$INVN > 0
AND T\$AMNT > 0
AND T\$INVD BETWEEN '2006-10-01'
AND '2007-09-30')) P
GROUP BY Project, CUSTOMER