
Oracle General Oracle database discussions. 
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com



March 26th, 2007, 07:56 AM

Registered User


Join Date: Feb 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts


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

March 26th, 2007, 10:29 AM

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 '20061001'
AND '20070930')) P
GROUP BY Project, CUSTOMER

