当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Error Converting data type Discount amount vs fixed amount

问题描述:

Good day

This is the code I am using and it gives an error "Error converting data type varchar to numeric"

select InvPrice.StockCode, InvPrice.SellingPrice,

SorContractPrice.StartDate, SorContractPrice.ExpiryDate, SorContractPrice.PriceMethod, SorContractPrice.FixedPrice, SorContractPrice.Discount1,InvPrice.SellingPrice

From InvPrice

Inner Join

SorContractPrice

on SorContractPrice.StockCode = InvPrice.StockCode

where SorContractPrice.PriceMethod IN

(case when SorContractPrice.PriceMethod = 'F' then SorContractPrice.FixedPrice

when SorContractPrice.PriceMethod = 'D' then InvPrice.SellingPrice - SorContractPrice.Discount1

else SorContractPrice.FixedPrice

end)

I need to deduct the discount from the fixed price alternatively it should stay as the fixed price.

Sorry I am very new to sql

Your assistance is highly appreciated.

网友答案:

You can use try_convert on one of the items that you are going to use to minus off. convert method works too. [If the first item doesn't work, you can try it on the alternate one. But if you already know which one holds the varchar value, then use it on that one.] You can basically convert both of them to the same value (float or numeric)

If you need a short guide on how to use those , here it is : http://sqlhints.com/2013/06/08/try_convert-sql-server-2012-built-in-conversion-function/

But remember that it will not convert it for you if it simply cant. If it can't it just means that the data you are using for varchar most likely isn't a number ,etc.

Also, what @KD commented, cast also helps too.

Hope this helps you out.

分享给朋友:
您可能感兴趣的文章:
随机阅读: