• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • 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
 
PortletPaulCommented:
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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