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
nussedogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
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
 
HuaMin ChenProblem resolverCommented:
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.

All Courses

From novice to tech pro — start learning today.