?
Solved

Why is CASE statment in SQL UPDATE not working

Posted on 2014-12-08
5
Medium Priority
?
116 Views
Last Modified: 2014-12-08
In the UPDATE statement below, the InspectionChg, MileageChg, FuelChg and FreightChg columns are not updating to temp table. Goal is to aggregate charges.

 What is wrong ???

thanks

UPDATE #TTable  
      SET ChargeCode    = id.cht_itemcode,
       InspectionChg = CASE WHEN LEFT(id.cht_itemcode, 3) = 'INS'  THEN InspectionChg+id.ivd_charge ELSE InspectionChg+0 END,
       MileageChg    = CASE WHEN LEFT(id.cht_itemcode, 4) = 'MILE' THEN MileageChg+id.ivd_charge ELSE MileageChg+0 END,
       FuelChg              = CASE WHEN LEFT(id.cht_itemcode, 4) = 'FUEL' THEN FuelChg+id.ivd_charge ELSE FuelChg+0      END,
       FreightChg    = CASE WHEN LEFT(id.cht_itemcode, 2) = 'LH'   THEN FreightChg+id.ivd_charge ELSE FreightChg+0 END,
        Rate = 1
 FROM #TTable
 INNER JOIN invoicedetail as id
    on #TTable.OrderNumber = id.Ord_hdrnumber
0
Comment
Question by:nussedog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 40487891
If you need an aggregated value, calculate the aggregate THEN set that into the temp table. You won't achieve an aggregate using the approach above.

Use a subquery on invoicedetail using SUM(case when ... end) to arrive at the aggregates.
0
 
LVL 25

Expert Comment

by:chaau
ID: 40487893
You can test the result by using the SELECT statement:
SELECT #TTable.ChargeCode, id.cht_itemcode,
#TTable.InspectionChg,
CASE WHEN LEFT(id.cht_itemcode, 3) = 'INS'  THEN InspectionChg+id.ivd_charge ELSE InspectionChg+0 END newInspectionChg,
#TTable.MileageChg, CASE WHEN LEFT(id.cht_itemcode, 4) = 'MILE' THEN MileageChg+id.ivd_charge ELSE MileageChg+0 END newMileageChg,
#TTable.FuelChg, CASE WHEN LEFT(id.cht_itemcode, 4) = 'FUEL' THEN FuelChg+id.ivd_charge ELSE FuelChg+0      END newFuelChg,
#TTable.FreightChg, CASE WHEN LEFT(id.cht_itemcode, 2) = 'LH'   THEN FreightChg+id.ivd_charge ELSE FreightChg+0 END newFreightChg,
#TTable.Rate
 FROM #TTable
 INNER JOIN invoicedetail as id
    on #TTable.OrderNumber = id.Ord_hdrnumber 

Open in new window

Can you see any result? Do you see difference between old and new values?
0
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 800 total points
ID: 40488019
There are a couple of questions about this basic update I was wondering about.

As written, it does not allow for multiple itemCode / ChargeCode values. Is it true that there will only be one?
Have you created an index on #TTable?  Especially if the dataset is large, the index is important.

Here is code that will handle both the possibility of multiple charge codes per run, and single charge codes - the underlying table does not need to be different in these cases. Obviously, I don't know your data types used, so I will make a best guess.

-- #TTable has already been populated here....



CREATE TABLE #TTable2 (
    OrderNumber int NOT NULL,
    itemcode varchar(12) NOT NULL,
    InspectionChg decimal(9,2) NOT NULL,
    MileageChg decimal(9,2) NOT NULL,
    FuelChg decimal(9,2) NOT NULL,
    FreightChg decimal(9,2) NOT NULL
    )

INSERT INTO #TTable2 (
    OrderNumber,
    itemcode,
    InspectionChg,
    MileageChg,
    FuelChg,
    FreightChg
    )
SELECT
    t.OrderNumber,
    id.cht_itemcode,
    InspectionChg = SUM(
        CASE 
            WHEN LEFT(id.cht_itemcode, 3) = 'INS'  
                THEN InspectionChg+id.ivd_charge 
            ELSE 0 
        END
        ),
    MileageChg = SUM(
        CASE 
            WHEN LEFT(id.cht_itemcode, 4) = 'MILE' 
                THEN MileageChg+id.ivd_charge 
            ELSE MileageChg+0 
        END
        ),
    FuelChg = SUM(
        CASE 
            WHEN LEFT(id.cht_itemcode, 4) = 'FUEL' 
                THEN FuelChg+id.ivd_charge 
            ELSE 0
        END
        ),
    FreightChg = SUM(
        CASE 
            WHEN LEFT(id.cht_itemcode, 2) = 'LH'
                THEN FreightChg+id.ivd_charge 
            ELSE 0 
        END
        )
FROM invoicedetail as id
INNER JOIN #TTable AS t
    on #TTable.OrderNumber = id.Ord_hdrnumber
GROUP BY OrderNumber,
    cht.itemcode

Open in new window

This may be overengineered for what you need, but I find that a bit of early overengineering frequently reduces problems related to changing user expectations.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40488021
Hi,
It can be a data problem. Check if the column

id.cht_itemcode

is having the expected values or not.
0
 

Author Closing Comment

by:nussedog
ID: 40488159
Assigning PortLetPaul 300 points for being first with correct answer, 200 points bhess1 for taking the effort to write out solution.

Thanks Everyone !
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question