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

Analyst_SQL 3,531 Reputation points

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 

left   join ConIssuance I 
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'
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,278 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points

    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.


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

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,711 Reputation points

    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.

  2. Beata Małodzińska 1 Reputation point

    • **

    > List item


    0 comments No comments