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

Question

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

Analyst_SQL 3,576

Here i am getting erro.
(C.ConWeight-iSNULL(Sum(I.IWeight),0)) as Pending_Weight,

with cte as(
SELECT   C.ContNo,C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty,
iSNULL(Sum(I.IWeight),0)Isu_Weight,iSNULL(Sum(i.QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.IWeight),0)) as Pending_Weight,
(C.No_Of_Bales-iSNULL(Sum(i.QTY),0)) as Pending_Qty
FROM     Containerno C
inner   join tbl_ContD CD 

on CD.CID=C.CID
left   join ConIssuance I 
on I.D_ID=CD.D_ID
where C.delid is null and I.Delid is null -- and C.entrydate>'2020-12-31'  and c.con_status is null
group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate
)
select ROW_NUMBER() OVER (ORDER BY ContNo) AS S_No,ContNo,COnvert(varchar,EntryDate,103) Date ,Act_weight,Act_Qty,Isu_Weight,Isu_QTY,Pending_Weight,Pending_Qty  from cte
where Pending_Qty>0 -- and EntryDate>'2020-12-31'
union all

Select '','','Total',  Sum(isnull(cast(Act_Weight as float),0)),Sum(isnull(cast(Act_Qty as float),0)),Sum(isnull(cast(Isu_Weight as float),0)),Sum(isnull(cast(Isu_QTY as float),0)),
 Sum(isnull(cast(Pending_Weight as float),0)) ,   Sum(isnull(cast(Pending_Qty as float),0))  from cte
 where Pending_Qty>0 -- and EntryDate>'2020-12-31'

Accepted answer

2 additional answers

Your answer

Answer 1

EchoLiu-MSFT 14,621

Hi @Analyst_SQL ,

In addition,the data types of the columns of the two select statements before and after union all must be the same. Please check for consistency.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Analyst_SQL 3,576

Please review query below ,just facing issue one this line

select isnull(convert(decimal(10,2), Pending_Weight),0),
Error is
Error converting data type nvarchar to numeric.Warning: Null value is eliminated by an aggregate or other SET operation.

with cte as(
SELECT  (isnull(cast(C.ConWeight as Decimal(10,0) ),0)-iSNULL(Sum(cast(I.IWeight as DECIMAL(10,2))),0)) as Pending_Weight,
(C.No_Of_Bales-iSNULL(Sum(i.QTY),0)) as Pending_Qty
FROM     Containerno C
inner   join tbl_ContD CD 

on CD.CID=C.CID
left   join ConIssuance I 
on I.D_ID=CD.D_ID
where C.delid is null and I.Delid is null
group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate
)
select isnull(convert(decimal(10,2), Pending_Weight),0), 
 (Pending_Qty)  from cte
where (Pending_Qty)>0 
union all

Select  
 Sum(isnull(convert( Decimal(10,2),Pending_Weight),0)) ,  
  Sum(isnull(Pending_Qty,0))  from cte

EchoLiu-MSFT 14,621 Reputation points

2021-05-21T02:55:01.977+00:00

Sorry, can you provide some test data. I did a test with my data, but unfortunately no error was returned.

EchoLiu-MSFT 14,621

select CAST(isnull(convert(decimal(10,2), Pending_Weight),0)AS CHAR), 
  (Pending_Qty)  
from cte
where (Pending_Qty)>0 
union all
Select  CAST(Sum(isnull(convert( Decimal(10,2),Pending_Weight),0))AS CHAR) ,  
   Sum(isnull(Pending_Qty,0))  
from cte

I saw someone used the above method to solve a similar problem, you can try.

Answer 2

Tom Phillips 17,771

We would need to know the schema of the tables involved.

My guess is Pending_Qty is an nvarchar. So Pending_Qty>0, needs to be CAST(Pending_Qty as INT)>0.

Analyst_SQL 3,576 Reputation points

2021-05-21T02:07:56.033+00:00

@Tom Phillips

Pending_qty column does not have decimal value,but Pending_weight has Decimal value and which in an nvarchar ,now i want to convert it to Decimal
cast(Pending_Weight as Decimal(10,0)),

But below error is coming
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.
EchoLiu-MSFT 14,621 Reputation points

2021-05-21T02:20:24.073+00:00

The cause of the error may be that the Pending_Weight column contains a null value.I suggest you post a small example for testing.

Answer 3

Beata Małodzińska 1

**

> List item

**

Share via

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

2 additional answers

Your answer