CODEWITHSUNDEEP
sql-server
John Spencer
John Spencer

Reputation: 380

Calculating 2 columns depending on 3rd columns value

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

Upvotes: 3

Views: 87

Answers (3)

user6691848
user6691848

Reputation:

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

Upvotes: 2

John Spencer
John Spencer

Reputation: 380

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

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 7

Related Questions