Add 2 decimal fields(columns) with a CASE in SQL Server

Question

Add 2 decimal fields(columns) with a CASE in SQL Server

User7766

SSCommitted

Points: 1969
More actions
November 9, 2016 at 9:19 pm

#322820

Hi All,
I've 4 decimal(15,6) columns in my table and based on the condition i am updating one of the four columns in sql server database which also includes summing up of 2 decimal columns. Below is the query for the same but getting a syntactial error while summing up. Can anyone please look into it.
UPDATE TrdOrder SET
TrxQty = CASE WHEN @ActionCode =0 THEN CAST(TrxQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6)),
CancelQty = (CASE WHEN @ActionCode =1 THEN CAST(CancelQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6))),
AdjustUpQty = (CASE WHEN @ActionCode=2 AND @TrxQty<0 THEN CAST(AdjustUpQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6))),
AdjustDownQty = (CASE WHEN @ActionCode=2 AND @TrxQty>0 THEN CAST(AdjustDownQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6)))
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

User7766 (11/9/2016)
Hi All,
I've 4 decimal(15,6) columns in my table and based on the condition i am updating one of the four columns in sql server database which also includes summing up of 2 decimal columns. Below is the query for the same but getting a syntactial error while summing up. Can anyone please look into it.
UPDATE TrdOrder SET
TrxQty = CASE WHEN @ActionCode =0 THEN CAST(TrxQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6)),
CancelQty = (CASE WHEN @ActionCode =1 THEN CAST(CancelQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6))),
AdjustUpQty = (CASE WHEN @ActionCode=2 AND @TrxQty<0 THEN CAST(AdjustUpQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6))),
AdjustDownQty = (CASE WHEN @ActionCode=2 AND @TrxQty>0 THEN CAST(AdjustDownQty AS DECIMAL(15,6))+CAST(@TrxQty AS DECIMAL(15,6)))
Thanks

What error are you getting?

😎

Can you post the DDL (create table) script, variable deceleration, sample data as an insert script and the desired results please?

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

A CASE statements needs to be terminated with the END keyword

😎

-- Syntax for SQL Server and Azure SQL Database

Simple CASE expression:

CASE input_expression

WHEN when_expression THEN result_expression [ ...n ]

[ ELSE else_result_expression ]

END

Searched CASE expression:

CASE

WHEN Boolean_expression THEN result_expression [ ...n ]

[ ELSE else_result_expression ]

END

User7766 SSCommitted Points: 1969 More actions · Answer 3

Eirikur Eiriksson (11/9/2016)
A CASE statements needs to be terminated with the END keyword
😎

Oops, Missed it out.. Thanks for the correction.:-)