Calculating 2 columns depending on 3rd columns value

Question

Hi I'm trying to calculate 2 columns values based upon a case depending on what is in a 3rd column.

the code below errors with Error converting datatype varchar to numeric.

I believe it is trying to set the Currency Column to the new value instead of test.

can anyone help on my syntax.

Thankyou.

SELECT  dbo.ORDR.DocTotal,
        dbo.ORDR.DocTotalFC,
        test = case


when dbo.RDR1.Currency = 'GBP' then dbo.ORDR.DocTotal - dbo.ORDR.VatSum
when dbo.RDR1.Currency = 'USD' then dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC
when dbo.RDR1.Currency = 'EUR' then dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC

else 'other'
end


FROM    dbo.RDR1 INNER JOIN
                     dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry

at a guess, i'd say either `DocTotal ` or `VatSum` are not numeric data types, hence the subtraction in the then clause is failing.... but you need to show us the schema and data, otherwise we're guessing. — Tanner, Apr 12 '17 at 13:35
You can't just arbitrarily decide to have the value in this column be varchar one on row and a number in the other rows. You MUST have a single datatype for a given column in ALL rows. Your case expression is doing math OR returning a string literal. You either need to return NULL there or force your calculated values to varchar. — Sean Lange, Apr 12 '17 at 13:35

Jaydip Jadhav · Answer 1 · 2017-04-12T13:49:45.477

7

The problem with else part of case expression

else 'other'

Since your case expression returns some integer type of values in other scenarios but in else part you are returning string values which in not compatible with previous values. try to replace else condition with some integer values as default value

edited Apr 12 '17 at 13:49

answered Apr 12 '17 at 13:34

Jaydip Jadhav

12,179
6
24
40

score 2 · Answer 2 · 2017-04-12T14:13:06.437

2

The error is being caused by your else condition where we are returning other. The column values at hand appear to be money or some form of decimal(x,x).

We cannot mix data types in a selected column. Therefore, we cannot mix types in a case statement as it returns a single column.

Ideally, You should set your else condition to a currency amount like 0.0 to not error out and to be consistent.

In the future, the else part of your case is a great first place to look for these errors, as you have seen per your comments. This is often where devs try and mix data types.

If you must return other, cast your other return values to varchar:

SELECT  dbo.ORDR.DocTotal,
        dbo.ORDR.DocTotalFC,
        test = case


when dbo.RDR1.Currency = 'GBP' then cast( (dbo.ORDR.DocTotal - dbo.ORDR.VatSum) as varchar(255))
when dbo.RDR1.Currency = 'USD' then cast( (dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC) as varchar(255))
when dbo.RDR1.Currency = 'EUR' then cast( (dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC) as varchar(255))

else 'other'
end


FROM    dbo.RDR1 INNER JOIN
                     dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry

edited Apr 12 '17 at 14:13

answered Apr 12 '17 at 13:36

1

Yes I removed the else comment as it didn't need to be there it was left over from some other code I had that I copied, It was staring me in the face and I missed it. Thanks for your help all. – John Spencer Apr 12 '17 at 13:46
Good call! That is probably the best thing to do. Returning the actual data type of the column is ideal. – Apr 12 '17 at 13:53
As this is working now with a select statement I wish to update an actual column in the table. 'dbo.ORDR.DiscSum', can you please point me in the right direction of changing my script, I can do it on a single table but am unsure how to do it with 2 tables joined. – John Spencer Apr 12 '17 at 13:57
1

That is a new question totally. Give it a try on your own first! This is a great place to help you get started: http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server – Apr 12 '17 at 14:00

score 1 · Answer 3 · answered Apr 12 '17 at 14:02

Got it. thank you for the link :)

update dbo.ORDR
set DiscSum = case 
when dbo.RDR1.Currency = 'GBP' then dbo.ORDR.DocTotal - dbo.ORDR.VatSum
when dbo.RDR1.Currency = 'USD' then dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC
when dbo.RDR1.Currency = 'EUR' then dbo.ORDR.DocTotalFC - dbo.ORDR.VatSumFC

end


FROM            dbo.RDR1 INNER JOIN
                     dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry

Calculating 2 columns depending on 3rd columns value

3 Answers3