Error converting data type nvarchar to numeric.

[Former Member]

Hi

When executing following query getting an error Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to numeric. Have marked the part in red causing the error in query

Select A.[Delivery No],A.[Delivery Date],A.[Item Code],

A.[Item Name],

A.[Batch Number] ,

A.[Sale Quantity],

((A.[Sale Value]/A.[Sale Quantity])+A.[Cen Vat]) as 'Sale Price',

cast(IsNull(A.[Sale Freight],0)as varchar) as 'Sales Freight',

A.[Sale Value],

A.RefNo,

A.RefDate,

A.PaymentTerms,

A.SalesDiscount,

A.[SalesTax],

A.[SalesTaxRate],

A.[TransportType] ,

A.[Make],

A.[Purchase Quantity],

A.[Vendor Ref Number],

A.[PO Number],

IsNull(A.[DiscPrcnt1],0) as 'DiscPrcnt',

(((A.[Purchase price]/(100-(IsNull(A.[DiscPrcnt1],0))))*100)) as 'P Price',

A.[CST]/A.[Purchase Quantity] as 'CSTORVAT',

A.AddDuty as 'Additional Duty',

A.CVDORBED as 'CVDORBED',

IsNull((A.[Total Purchase Freight]/A.[Purchase Quantity]),0) as 'Purchase Freight',

IsNUll(A.PurchaseDiscount,0) as 'Purchase Discount',

IsNull(A.[InterestDays],0) 'InterestDays',

IsNUll((((((A.[Purchase price]/(100-(IsNull(A.[DiscPrcnt1],0))))*100)+ (A.[Sale Freight])-(A.[PurchaseDiscount])+(A.[CST]/A.[Purchase Quantity])

+(A.[Total Purchase Freight]/ A.[Purchase Quantity])+((A.[AddDuty]/A.[Purchase Quantity])+(A.[CVDORBED]/A.[Purchase Quantity])))*0.18)/365*A.[InterestDays]),0)

as 'Interest',

((A.[AddDuty]/A.[Purchase Quantity])+(A.[CVDORBED]/A.[Purchase Quantity])) as 'Cen Vat',A.[Vendor Name],

A.[Customer Name],

IsNull(A.[AgentCommission],0) as 'AgentCommission',

A.[CustomerAddress],

A.[Cen Vat]

from (Select ODLN.DocNum as 'Delivery No' , ODLN.DocDate as 'Delivery Date' , DLN1.ItemCode as 'Item Code' , OITM.ItemName as 'Item Name' ,

IBT1.BatchNum as 'Batch Number' , DLN1.Quantity as 'Sale Quantity' , OCRD.CardName as 'Customer Name' , OPDN.U_Discount as 'PurchaseDiscount' ,ODLN.[U_AgntCom] as 'AgentCommission', ODLN.Address as 'CustomerAddress',

ODLN.U_InterestDesc as 'InterestDays' ,

IsNull((select SUM(TaxSum) from Pdn4 where Pdn4.DocEntry=Opdn.DocEntry and staType=4),0) as 'CST' ,

(Select SUM(T.U_AAdd) From [@GEN_TRADE] T Where T.U_BDocNo = OPDN.DocNum) as 'AddDuty' ,

(Select SUM(ISNULL(T.U_ABED,0) + ISNULL(T.U_ACVD,0) + ISNULL(T.U_ACoCVD,0) + ISNULL(T.U_AHoCVD,0) + ISNULL(T.U_ACoBED,0) + ISNULL(T.U_AHoBED,0))

From [@GEN_TRADE] T Where T.U_BDocNo = OPDN.DocNum) as 'CVDOrBED' , DLN1.Quantity * DLN1.Price as 'Sale Value', ODLN.NumAtCard as 'RefNo',DLN1.U_Make as 'Make',

ODLN.TaxDate as 'RefDate',IsNull(ODLN.U_Payment,0) as 'PaymentTerms',OBTN.U_CenVat as 'Cen Vat',

(select Top 1 IsNull(DLN4.TaxRate, 0) from Dln4 where DLN4.DocEntry=Odln.DocEntry) as 'SalesTaxRate',

(select Top 1 IsNull(DLN4.StcCode, 0) from Dln4 where DLN4.DocEntry=Odln.DocEntry) as 'SalesTax',

IsNull(ODLN.U_Discount,0) as 'SalesDiscount',

ODLN.[U_Transport]as 'TransportType',

(Select SUM(DLN3.LineTotal) From DLN3 Where DLN3.DocEntry = ODLN.DocEntry) as 'Sale Freight' ,

(Select SUM(PDN3.LineTotal) From PDN3 Where PDN3.DocEntry = OPDN.DocEntry And PDN3.ExpnsCode = '3') as 'Total Purchase Freight' ,

PDN1.Quantity as 'Purchase Quantity' , PDN1.Price as 'Purchase price', PDN1.DiscPrcnt as 'DiscPrcnt1' , PDN1.Quantity * PDN1.Price as 'Purchase Value' ,

OPDN.CardName as 'Vendor Name' , PDN4.TaxRate as 'Purchase Rate', OPDN.DocNum  as 'Vendor Ref Number',PDN1.BaseRef as 'PO Number'

From ODLN Inner Join DLN1 On ODLN.DocEntry = DLN1.DocEntry

Inner Join IBT1 On DLN1.DocEntry = IBT1.BaseEntry And IBT1.BaseType = '15' And IBT1.ItemCode = DLN1.ItemCode

Inner Join OITM On OITM.ItemCode = DLN1.ItemCode

Inner Join OCRD On OCRD.CardCode = ODLN.CardCode

Inner Join OIBT On OIBT.BatchNum = IBT1.BatchNum And OIBT.ItemCode = IBT1.ItemCode And OIBT.BaseType = '20'

Inner Join OPDN On OIBT.BaseEntry = OPDN.DocEntry

Inner Join PDN1 On OPDN.DocEntry = PDN1.DocEntry And PDN1.ItemCode = DLN1.ItemCode

Left Outer Join PDN4 On PDN4.DocEntry = PDN1.DocEntry And PDN1.LineNum = PDN4.LineNum

Left Outer Join OBTN On OBTN.DistNumber=IBt1.BatchNum)A

Regards,

Karthik B

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please confirm ODLN.U_InterestDesc is text or number.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Its  a text field

Regards,

Karthik

[kothandaraman_nagarajan · Accepted solution]

Then how we can multiply text field with number? See highlighted area. Try to change   ODLN.U_InterestDesc to numeric type.

IsNUll((((((A.[Purchase
price]/(100-(IsNull(A.[DiscPrcnt1],0))))*100)+ (A.[Sale
Freight])-(A.[PurchaseDiscount])+(A.[CST]/A.[Purchase Quantity])

+(A.[Total Purchase Freight]/ A.[Purchase
Quantity])+((A.[AddDuty]/A.[Purchase
Quantity])+(A.[CVDORBED]/A.[Purchase
Quantity])))*0.18)/365*A.[InterestDays]),0)

as 'Interest',