'Error Converting Varchar to Numeric Syntax
I'm trying to build code that will generate a file in a specific format. The code worked in its entirety until I added A.PrevYTDSales and A.YtdQtySold, so I'm not sure what's going on. It now gives me an error saying "error converting varchar to numeric" so I'm assuming I have a syntax error somewhere but I can't find where. Below is the code.
select
Inv.StockCode -- part
+ '|' + Inv.Description -- desc
+ '|' + isnull(
replace(
CONVERT(varchar,
CAST(
isnull(
case when 0 > (Inv.QOH - isnull(Sor.QOO,0)) then 0
else (Inv.QOH - isnull(Sor.QOO,0)) end
,0)
as Money)
, 1)
,'.00','')
,'0') -- QOH
+ '|' + replace(convert(varchar,cast(BoxQty as money),1),'.00','') -- box qty
+ '|' + YTDSales -- YTD Qty Sold
+ '|' + PrevYTDSales -- Prev YTD Qty Sold
+ '|' + convert(varchar,TRY_CAST(Mass as NUMERIC(19,4))) -- mass
+ '|' + isnull(convert(varchar,DueDt+3,101),' ') -- duedt
+ '|' +
case when isnull(convert(varchar,DueDt+3,101),' ') = ' ' then ' '
when 0 > (Inv.QOH - isnull(Sor.QOO,0)) then
isnull(
replace(
CONVERT(varchar,
CAST(
isnull(
case when 0 > (GitQty - isnull(Sor.QOOtoPOS,0)) then 0
else (GitQty - isnull(Sor.QOOtoPOS,0)) end
,0)
as Money)
, 1)
,'.00','')
,'0')
else
replace(convert(varchar,convert(money,GitQty),1),'.00','')
end -- QTY coming
+ '|' + COO -- COO
+ '|' + Tariff -- tariff
+ '|' + Location -- Customer
+ '|' + isnull(Cus.Customer,' ') -- customer
+ '|' + isnull(
replace(
replace(
Cus.CustStockCode
,Char(13),'')
,Char(10),'')
,' ') customer part
+ '|' + isnull(Cus.StockCode,' ')
+ '|' + 'R'
as partlist
from (
select
A.StockCode as StockCode
,B.Description as Description
,Sum(QtyOnHand) as QOH
,B.UserField2 as BoxQty
,A.YtdQtySold as YTDSales
,A.PrevYearQtySold as PrevYTDSales
,B.Mass as Mass
,B.ProductClass
,B.CountryOfOrigin as COO
,B.TariffCode as Tariff
,case
when Warehouse = '01' then 'All W/H'
when Warehouse = '02' then 'All W/H'
else ''
end as Location
from CompanyR.dbo.InvWarehouse A
left join CompanyR.dbo.InvMaster B on A.StockCode = B.StockCode
left join CompanyR.dbo.[InvMaster+] C on B.StockCode = C.StockCode
where Warehouse in ('01', '02')
and A.StockCode not like 'TEC%'
and B.StockCode is not null
group by A.StockCode , B.Description, B.UserField2, A.YtdQtySold, A.PrevYearQtySold, Mass, B.ProductClass
,B.CountryOfOrigin
,B.TariffCode
,case
when Warehouse = '01' then 'All W/H'
when Warehouse = '02' then 'All W/H'
else ''
end
) Inv
LEFT JOIN
(
select
sum(case
when (Getdate()+57) < MLineShipDate then 0
else (MOrderQty)
end
) as QOO,
sum(case
when ( MLineShipDate > isnull(DueDt, GetDate() ) )
AND
( MLineShipDate < isnull(POSDt, GetDate()+57) )
then MOrderQty
else 0
end
) as QOOtoPOS,
DueDt,
GitQty,
POSDt,
A.MStockCode as StockCode
from CompanyR.dbo.SorDetail A
left join CompanyR.dbo.SorMaster B on A.SalesOrder = B.SalesOrder
left join (
select StockCode
,(ExpectedDueDate) as DueDt
,SUM(GtrQuantity-QtyReceived) as GitQty
from CompanyR.dbo.GtrDetail A
where TransferComplete <> 'Y'
and CompanyR.dbo.get_week(ExpectedDueDate,0) >= CompanyR.dbo.get_week(GETDATE(),0)
and ExpectedDueDate = (select Min(ExpectedDueDate)
from CompanyR.dbo.GtrDetail B where TransferComplete <> 'Y' and A.StockCode = B.StockCode
and CompanyR.dbo.get_week(ExpectedDueDate,0) >= CompanyR.dbo.get_week(GETDATE(),0) )
group by StockCode, ExpectedDueDate
) GIT on A.MStockCode = GIT.StockCode
left join (
select
MStockCode
,min(MLatestDueDate) as POSDt
from CompanyR.dbo.PorMasterDetail
where MCompleteFlag <> 'Y'
and MLatestDueDate > getdate()
and MStockCode not like '-%'
and MStockCode not like '#%'
and MStockCode not like '*%'
group by MStockCode
) POS on A.MStockCode = POS.MStockCode
where 1=1
and A.MLineShipDate < dateadd(week,8,getdate())
and A.MLineShipDate >= DATEADD(s, -0,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
and A.MStockCode not like '*%'
and A.MStockCode not like '-%'
and B.OrderStatus not in ('\','*','9')
group by A.MStockCode,DueDt,POSDt,GitQty
) Sor on Inv.StockCode = Sor.StockCode
LEFT JOIN CompanyR.dbo.ArCustStkXref Cus on Inv.StockCode = Cus.StockCode and Cus.Customer <> ''
It's supposed to output something like this
098-01-PK|#6 , 3MM DL WASHER 200 PAIR PK|0|0|1.0000| | |TWN|7318.22.0000|All W/H| | | |L
098-01-PK|#6 , 3MM DL WASHER 200 PAIR PK|0|0|1.0000| | |TWN|7318.22.0000|| | | |L
098-01|#6 , 3MM DL WASHER PAIR|0|3,000|1.0000| | |TWN|7318.22.0000|All W/H| | | |L
098-01|#6 , 3MM DL WASHER PAIR|0|3,000|1.0000| | |TWN|7318.22.0000|| | | |L
Anyone have any ideas where I could be having an issue?
Solution 1:[1]
Apparently the only conversion to numeric that you have is for the Mass, column.
Since you added the new columns into the group by, probably new rows would appear showing the Mass column with (maybe) a null or some new strange value.
Try running the query from the query inside the from clause by itself and then looking for distinct values for the Mass column and check if they all are numeric.
Hope it helps!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Rodrigo Cava |