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

Analyst_SQL 3,531 Reputation points
2021-05-20T18:30:14.333+00:00

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'
 
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
Transact-SQL
Transact-SQL
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
    2021-05-21T02:01:22.467+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,711 Reputation points
    2021-05-20T18:39:59.86+00:00

    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
    2021-06-07T02:04:16.527+00:00


    • **

    > List item

    **

    0 comments No comments