Conversion failed when converting the nvarchar value 'EA' to data type int.

Question

Hi i have some conversion issue while executing this select stmt in my console,request you to please provide your assistance here

here the issue with the RKME filed ,where it is having nvarchar data type,i tried using the cast and convert functions but not resolved the issue,ETPR is the decimal column

Select
,CASE
WHEN ([RKME]) not in ('LB','#','feet','FT','QP','OU') and ([EME] is not null and [EME] <> 0) THEN ([ETPR] * [ENG]/[EME]) * -1
ELSE [ETR] * -1 END as TotalAmount

      ,GETDATE() as InsertDate

From dbo.SALES

please provide the asistsnce to resolve this conversion issue

Answer 1

CREATE TABLE [dbo].SALES(

[ETPR] [decimal](18, 2) NULL,  
[ENG] [decimal](15, 3) NULL,  
[KME] [nvarchar](10) NULL,  
[EME] [nvarchar](3) NULL,  
[ETR] [decimal](24, 9) NULL,  
)  

sample data

Answer 2

[EME] nvarchar NULL,
[EME] <> 0

There it is, you comape a nvarchar with fix integer 0, that cause the conversion error. Change 0 to a string '0' =>

[EME] <> '0'

Answer 3

You might want to have a look at TRY_CAST() to see if it will help with he rest of your query.

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql

divide my EME is too risky without it (I assume once in a while divide by EME works but most times not)

Answer 4

Hi @Naresh y
We cannot calculate division using 'EA' as divisors, unless what you mean it is a hex number.
If i guess right ,then we need to convert 'EA' to decimal .
Have a check on this:

SELECT CONVERT ( bigint , CONVERT ( varbinary , CAST (N'0x' + [EME] AS char ), 1 ))  
From dbo.SALES  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.