View Single Post
 
Old March 26th, 2007, 07:56 AM
tnzabo tnzabo is offline
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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, '2006-10-01 00:00:00', 102)
            AND CONVERT(DATETIME, '2007-09-30 00:00:00', 102))) P
GROUP BY Project, CUSTOMER