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

Answer 1

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

Answer 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