Hi
Having a problem converting data. This is the query I'm using that's failing with Msg 8114 level 16 state 5: "Error converting data type nvarchar to numeric":
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) as 'COSS Workorder',
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.DataValue,0) as decimal(10,3)) - cast(ISNULL(machweight.DataValue,0) as decimal(10,3)) as PoundsRemoved,
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweight.DataValue,0)) as 'FORGEVALUETEST',
ISNUMERIC(ISNULL(machweight.DataValue,0)) as 'MACHVALUETEST',
CAST(ISNULL(forgweight.datavalue,0) as numeric(15,5)) as 'Forge Weight',
CAST(ISNULL(machweight.datavalue,0) as numeric(15,5)) as 'Machine Weight',
-- case when CAST(ISNULL(forgweight.datavalue,0) as decimal(10,3)) > '1' then CAST(ISNULL(forgweight.datavalue,0) as decimal(10,3)) - CAST(ISNULL(machweight.datavalue,0) as decimal(10,3))
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.ActualEndOperation) as varchar) as MonthYear
-- case when e.ForgeWeight<e.MachineWeight then '0' else e.forgeweight-e.machineweight end as PoundsRemoved
--e.ForgeWeight-e.MachineWeight as Pounds
--(CONVERT(INT,REPLACE(left(e.ForgeWeight, len(e.ForgeWeight) - charindex('.', reverse(e.ForgeWeight))),',','')) -
--CONVERT(INT,REPLACE(left(e.MachineWeight, len(e.MachineWeight) - charindex('.', reverse(e.MachineWeight))),',',''))) as PoundsRemoved
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.qryRopeItems a
JOIN [houdev\coss].DBROld.dbo.tblWorkorders c
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.tblWorkorderStatus d
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders_Dimensions e
-- on LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) = e.WorkOrderNumber
--on RIGHT(RTRIM(c.WorkOrderNumber),11) = RIGHT(RTRIM(e.WorkOrderNumber),11)
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) = RTRIM(e.UsrDfnId)
LEFT OUTER JOIN forg.dbo.COSS_CustomAttributeValue forgweight
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttributeValue machweight
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7), a.actualendoperation, forgweight.DataValue, machweight.DataValue
I can comment out: CAST(ISNULL(forgweight.datavalue,0) as numeric(15,5)) as 'Forge Weight',
and if I do, the query runs without any issues.
I've tried running this to find out whether there's a non-numeric value in there, but I get no results:
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) as 'COSS Workorder',
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.DataValue,0) as decimal(10,3)) - cast(ISNULL(machweight.DataValue,0) as decimal(10,3)) as PoundsRemoved,
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweight.DataValue,0)) as 'FORGEVALUETEST',
ISNUMERIC(ISNULL(machweight.DataValue,0)) as 'MACHVALUETEST',
--CAST(ISNULL(forgweight.datavalue,0) as numeric(15,5)) as 'Forge Weight',
CAST(ISNULL(machweight.datavalue,0) as numeric(15,5)) as 'Machine Weight',
-- case when CAST(ISNULL(forgweight.datavalue,0) as decimal(10,3)) > '1' then CAST(ISNULL(forgweight.datavalue,0) as decimal(10,3)) - CAST(ISNULL(machweight.datavalue,0) as decimal(10,3))
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.ActualEndOperation) as varchar) as MonthYear
-- case when e.ForgeWeight<e.MachineWeight then '0' else e.forgeweight-e.machineweight end as PoundsRemoved
--e.ForgeWeight-e.MachineWeight as Pounds
--(CONVERT(INT,REPLACE(left(e.ForgeWeight, len(e.ForgeWeight) - charindex('.', reverse(e.ForgeWeight))),',','')) -
--CONVERT(INT,REPLACE(left(e.MachineWeight, len(e.MachineWeight) - charindex('.', reverse(e.MachineWeight))),',',''))) as PoundsRemoved
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.qryRopeItems a
JOIN [houdev\coss].DBROld.dbo.tblWorkorders c
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.tblWorkorderStatus d
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders_Dimensions e
-- on LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) = e.WorkOrderNumber
--on RIGHT(RTRIM(c.WorkOrderNumber),11) = RIGHT(RTRIM(e.WorkOrderNumber),11)
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7) = RTRIM(e.UsrDfnId)
LEFT OUTER JOIN forg.dbo.COSS_CustomAttributeValue forgweight
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttributeValue machweight
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN(c.WorkOrderNumber)-7), a.actualendoperation, forgweight.DataValue, machweight.DataValue
Out of ideas. Please let me know what you think