Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Why is CASE statment in SQL UPDATE not working

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
nussedog
Asked:
nussedog
2 Solutions
 
PaulCommented:
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
 
chaauCommented:
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
 
Brendt HessSenior DBACommented:
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
 
HuaMinChenBusiness AnalystCommented:
Hi,
It can be a data problem. Check if the column

id.cht_itemcode

is having the expected values or not.
0
 
nussedogAuthor Commented:
Assigning PortLetPaul 300 points for being first with correct answer, 200 points bhess1 for taking the effort to write out solution.

Thanks Everyone !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now