SQL optimization Case statement

Question

I believe I can optimize this sql statement by using a case statement for the Left Outer Joins.

But I have been having hard time setting up the cases, one for summing up the code types AB,CD and another for All the rest.

Appreciate any help or tips you can give me on this.

update billing set payments = isnull(bd1.amount, payments)
, payments = case 
       when payments is null then 0 
       else payments 
     end
, charges = case 
        when bd2.amount is not null then charges 
        when charges is null then 0 
        else charges 
      end
, balance = round(charges + isnull(bd1.amount, bi.payments), 2) 
from billing bi 

left outer join (select inv, round(sum(bd1.bal), 2) amount 
                from "bill" bd1 
                where code_type = 'AB'
                 or code_type = 'CD' 
                group by inv) bd1 
                on bd1.inv = bi.inv 
left outer join (select invoice, round(sum(bd2.bal), 2) amount 
                from "bill" bd2 
                where code_type <> 'AB'
                 and code_type <> 'CD' 
                group by inv) bd2 
                on bd2.inv = bi.inv;

Is this valid? You seem to be updating the `payments` column twice. — ypercubeᵀᴹ, Apr 30 '12 at 14:48

Conrad Frix · Accepted Answer · 2012-05-01T14:46:08.380

5

You can simplify it to this to use a single query rather than two. You still need the one because a GROUP BY in an UPDATE doesn't work.

UPDATE bi 
SET    payments = bd.payments, 
       charges= bd.charges, 
       balance = bd.balance 
FROM   billing bi 
       LEFT JOIN (SELECT bd.inv, 
                         payments = Round(Sum(CASE 
                                                WHEN code_type IN ( 'AB' , 'CD' ) THEN 
                                                bd.bal 
                                                ELSE 0 
                                              END), 2), 
                         charges = Round(Sum(CASE 
                                               WHEN code_type NOT IN ( 'AB' , 'CD' ) THEN 
                                               bd.bal 
                                               ELSE 0 
                                             END), 2), 
                         balance = Round(Sum(bd.bal), 2) 
                  FROM   bill bd 
                  GROUP  BY bd.inv) bd 
         ON bd.inv = bi.inv

edited May 01 '12 at 14:46

answered Apr 30 '12 at 14:57

Conrad Frix

51,984
12
96
155

This is not valid syntax. You can't have an aggregate function directly in the set list. – GarethD Apr 30 '12 at 15:06
Yeah, sorry, I started writing the comment, then checked to be 100% certain it wasn't valid, so by the time I actually posted you had already fixed it. You could improve this further by using `CASE WHEN Code_Type IN ('AB', 'CD')` Rather than `Code_Type = 'AB' OR Code_Type = 'CD'` – GarethD Apr 30 '12 at 15:12
Thank you this is just what I am looking for. Maybe i'm missing something but unfortunately I'm getting an error when trying to run the query. "Expected lexical element not found: FROM You are missing the keyword FROM after the column definitions in your SELECT statement. There was a problem parsing the table names after the FROM keyword in your UPDATE statement" Thanks – Trevor Apr 30 '12 at 16:05
@Trevor Do you have an extra comma somewhere? – Conrad Frix Apr 30 '12 at 16:14
@ConradFrix nope, maybe its an issue with the Database Management System i'm using. I'll work with it. – Trevor Apr 30 '12 at 16:37
@Trevor have you copied the above exactly? Have just noticed that the query in this answer tries to set payments three times, rather than payments once, charges once and balance once. That could be your problem (although based on the error message I am not confident). – GarethD May 01 '12 at 14:30
@GarethD doh copy and paste error got me agiain. Thanks for pointing that out – Conrad Frix May 01 '12 at 14:47
Thanks for the updates guys, unfortunately i'm still getting an error in advantage, but it all looks good to me. I appreciate the help, this is definitely what i'm looking for, maybe i'll post the code you provided and post it under and advantage tag and see if anyone can give me insight on why its not working. Thanks again! – Trevor May 02 '12 at 14:40

score 1 · Answer 2 · answered Apr 30 '12 at 14:50

Maybe something like this:

update billing set payments = isnull(bd1.amount, payments)
, payments = isnull(payments, 0)
, charges = isnull(bd2.amount, isnull(charges, 0))
, balance = round(charges + isnull(bd1.amount, bi.payments), 2)
from billing bi 

left outer join (select inv, round(sum(bd1.bal), 2) amount 
                from "bill" bd1 
                where code_type in ('AB', 'CD')
                group by inv) bd1 
                on bd1.inv = bi.inv 
left outer join (select invoice, round(sum(bd2.bal), 2) amount 
                from "bill" bd2 
                where code_type not in ('AB', 'CD')
                group by inv) bd2 
                on bd2.inv = bi.inv;

The two left joins are not a problem!

SQL optimization Case statement

2 Answers2