question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked BeataMaodziska-5892 answered

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @akhterhussain-3167,

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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



0 Votes 0 ·

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

1 Vote 1 ·
 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.

1 Vote 1 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered EchoLiu-msft edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TomPhillips-1744

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.

0 Votes 0 ·

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.

0 Votes 0 ·
BeataMaodziska-5892 avatar image
0 Votes"
BeataMaodziska-5892 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.