Case..When..Statement 1

[paljnad]

Hello,

I have this query that I run on Oracle 9i:

SELECT DISTINCT
b.amount_code_description,
to_char((CASE
WHEN b.year = '2005' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2005,
to_char((CASE
WHEN b.year = '2006' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2006,
to_char((CASE
WHEN b.year = '2007' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2007
FROM table a

and my result set looks like:
Y2005 Y2006 Y2007
ABC 0.00 0.00 25335.69
ABC 0.00 60868.80 0.00
ABC 15000.05 0.00 0.00

But I would like my result to look like:
Y2005 Y2006 Y2007
ABC 15000.05 60868.80 25335.69

I tried using Sum in the query like this:

SELECT DISTINCT
b.amount_code_description,
to_char(SUM(CASE
WHEN b.year = '2005' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2005,
to_char(SUM(CASE
WHEN b.year = '2006' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2006,
to_char(SUM(CASE
WHEN b.year = '2007' THEN
to_char(b.ytd_amount, '9999999999.99')
ELSE
'0.00'
END),
'999999999990.99') AS Y2007
FROM table a


but then the numbers are all wrong:
Y2005 Y2006 Y2007
ABC 660002.20 2678227.20 1114770.36

 

[SantaMufasa]

Paljnad,

Without assessing for performance improvements, the easiest method to achieve your results seems to me to place your already working (original) code into an "in-line view" as I have done here:

select amount_code_description
      ,sum(Y2005) Y2005
      ,sum(Y2006) Y2006
      ,sum(Y2007) Y2007
  from
(SELECT DISTINCT 
                b.amount_code_description,
                to_char((CASE
                              WHEN b.year = '2005' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2005,
                to_char((CASE
                              WHEN b.year = '2006' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2006,
                to_char((CASE
                              WHEN b.year = '2007' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2007
  FROM table_a)
 group by amount_code_description
/

Let us know the outcome of this method.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[paljnad]

Thanks, Mufasa - It works !

 

[jimirvine]

Why not just

SELECT DISTINCT
                b.amount_code_description,
                to_char(sum(CASE
                              WHEN b.year = '2005' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2005,
                to_char(sum(CASE
                              WHEN b.year = '2006' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2006,
                to_char(sum(CASE
                              WHEN b.year = '2007' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2007
  FROM table_a

 

[SantaMufasa]

Without assessing for performance improvements...

Had I taken the time that JimIrvine took to assess your logic, I (hopefully) would have seen that your original set of code that you discarded, should work fine. Here is a proof-of-concept for what I'm saying:

select * from table_a;

AMOUNT_CODE_DESCRIPTION       YEAR YTD_AMOUNT
----------------------- ---------- ----------
ABC                           2005   15000.05
ABC                           2006    60868.8
ABC                           2007   25335.69
DEF                           2005 18750.0625
DEF                           2006      76086
DEF                           2007 31669.6125

6 rows selected.

(Your discarded, but syntactically corrected code)
SELECT DISTINCT
                b.amount_code_description,
                to_char(SUM(CASE
                              WHEN b.year = '2005' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2005,
                to_char(SUM(CASE
                              WHEN b.year = '2006' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2006,
                to_char(SUM(CASE
                              WHEN b.year = '2007' THEN
                               to_char(b.ytd_amount, '9999999999.99')
                              ELSE
                               '0.00'
                            END),
                        '999999999990.99') AS Y2007
  FROM table_a b
 group by amount_code_description;

AMOUNT_CODE_DESCRIPTION            Y2005            Y2006            Y2007
----------------------- ---------------- ---------------- ----------------
ABC                             15000.05         60868.80         25335.69
DEF                             18750.06         76086.00         31669.61

2 rows selected.

All I did to cause your code to produce correct results was to fix the three syntax errors in your original, discarded code:[ul][li]
"table a" -> "table_a"[/li][li]"table_a" -> "table_a b"[/li][li]Added "group by amount_code_description"[/li][/ul]

So, the original goofy results you received are due to something (about your data or code) that you are not disclosing to us. <smile>

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[paljnad]

Will keep you posted.

Thanks again