Calculation of 2 columns depending on the value of the 3rd column
Hey. I am trying to calculate 2 column values based on a case depending on what is in the third column.
code below error with error converting varchar data type to numeric.
I believe it is trying to set the currency column to a new value instead of a test.
can someone help with 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
source to share
The problem with the other part of the case expression
else 'other'
Since the case expression returns some integer value type in other scenarios, but in the other part, you are returning string values that are incompatible with the previous values. try replacing the else condition with multiple defaults
source to share
The error was caused by the else clause in which we return other
. The column values at hand look like money
or some form decimal(x,x)
.
We cannot mix data types in the selected column. Therefore, we cannot mix types in a case statement because it returns a single column.
Ideally, you should set the else clause to be the amount of the currency, for example 0.0
, so it doesn't go out of order and be consistent.
In the future, some of else
yours case
is a great first place to look for these errors, as you saw in your comments. This is often the case when developers are trying to mix data types.
If you must return other
, add 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
source to share
Got it. Thanks 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
source to share