Solved

Why is CASE statment in SQL UPDATE not working

Posted on 2014-12-08
5
115 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 300 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:bhess1
bhess1 earned 200 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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