Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old March 26th, 2007, 07:56 AM
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

  #2 (permalink)  
Old March 26th, 2007, 10:29 AM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
update multiple column based on case statement?? vinod_yadav1919 SQL Server 2000 6 July 3rd, 2008 07:29 PM
multiple column update query with Case vinod_yadav1919 SQL Language 4 July 3rd, 2008 06:53 PM
How to get the Sum of all the values in a column.. jhansib4u ADO.NET 1 December 1st, 2007 09:15 PM
Sum a column at the group level? srussell705 SharePoint Development 0 October 3rd, 2007 08:40 AM
Sum a column from complex recordset shogg Access ASP 1 May 5th, 2005 07:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.