Using CASE in SUM column
Hello  I'm trying to get the following code to work and it keeps breaking down on the first CASE. I'm wondering if you can even do the CASE on the SUM column of this query. The error I'm getting is "missing right parenthesis". I've of course been making sure all the parens match and I've been adding and deleting them everywhere I can think that would make sense. Any help would be greatly appreciated. Thanks!
CREATE OR REPLACE VIEW FRT_SALES AS
SELECT CUSTOMER,
PROJECT,
(SUM(CASE P.MONTH WHEN 1 THEN P.AMOUNT ELSE 0 END) AS OCT),
(SUM(CASE P.MONTH WHEN 2 THEN P.AMOUNT ELSE 0 END) AS NOV),
(SUM(CASE P.MONTH WHEN 3 THEN P.AMOUNT ELSE 0 END) AS DEC),
(SUM(CASE P.MONTH WHEN 4 THEN P.AMOUNT ELSE 0 END) AS JAN),
(SUM(CASE P.MONTH WHEN 5 THEN P.AMOUNT ELSE 0 END) AS FEB),
(SUM(CASE P.MONTH WHEN 6 THEN P.AMOUNT ELSE 0 END) AS MAR),
(SUM(CASE P.MONTH WHEN 7 THEN P.AMOUNT ELSE 0 END) AS APR),
(SUM(CASE P.MONTH WHEN 8 THEN P.AMOUNT ELSE 0 END) AS MAY),
(SUM(CASE P.MONTH WHEN 9 THEN P.AMOUNT ELSE 0 END) AS JUN),
(SUM(CASE P.MONTH WHEN 10 THEN P.AMOUNT ELSE 0 END) AS JUL),
(SUM(CASE P.MONTH WHEN 11 THEN P.AMOUNT ELSE 0 END) AS AUG),
(SUM(CASE P.MONTH WHEN 12 THEN P.AMOUNT ELSE 0 END) AS SEP)
FROM (SELECT CUSTOMER,
PROJECT,
CAST(AMOUNT AS float) AS AMOUNT,
DATEPART('month', INVOICEDATE) 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 (INVOICENUMBER > 0)
AND (CAST(AMOUNT AS float) > 0)
AND INVOICEDATE BETWEEN CONVERT DATETIME, '20061001 00:00:00', 102)
AND CONVERT(DATETIME, '20070930 00:00:00', 102))) P
GROUP BY Project, CUSTOMER
