AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Mystical_Ice
Mystical_Ice
 asked on

SQL converting data problem (nvarchar to numeric)

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
Microsoft SQL ServerSQL
Avatar of undefined
Last Comment
damey
8/22/2022 - Mon
Mystical_Ice
ASKER
Instead of:
CAST(ISNULL(forgweight.datavalue,0) as numeric(15,5))
I have also tried:
CAST(ISNULL(forgweight.datavalue,0) as decimal(15,5)) , etc. with the same result
Mystical_Ice
ASKER
Attaching a list of the output of the two columns when just pulling them and doing no conversion:
(
      machweight.datavalue as 'Machine Weight',
      forgweight.datavalue as 'Forge Weight',
)

I don't see any values in there that are causing it to fail
Book1.xlsx
ste5an
E.g.
SELECT *
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 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  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'
	   AND ISNUMERIC(forgweight.datavalue) = 0;

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Patrick Matthews
You've got a non-null value somewhere that cannot be converted to a numeric type.  If you are on SQL Server 2012 or later, you can try something like this to find it:

SELECT *
FROM forgweight
WHERE TRY_PARSE(datavalue AS decimal(15, 5) IS NULL

Open in new window

Mystical_Ice
ASKER
I attached the values above in an excel file.  Don't see anything that could be causing it.
Mystical_Ice
ASKER
Also, this is on SQL 2008R2
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ste5an
What does my query return?
Mystical_Ice
ASKER
Retrieves no data
ste5an
And

SELECT CAST(ISNULL(forgweight.datavalue,0) AS NUMERIC(15,5)) 
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 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  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'
	   AND ISNUMERIC(ISNULL(forgweight.datavalue,0)) = 1;

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mystical_Ice
ASKER
Msg 8114, Level 16, State 5, Line 3
Error converting data type nvarchar to numeric.
ste5an
Then there is really a value which is considered to be numeric, but it's not convertible. This may happen, cause the ISNUMERIC() and ISDATE() functions are pretty liberal when testing strings.

The TRY_PARSE(), TRY_CAST(), TRY_CONVERT() methods are better, cause they really try to do the conversion first, but throw no error.

So you need to test your data in batches to find the problematic value.

E.g.

DECLARE @ROWS_PER_PAGE AS INT;
SET @ROWS_PER_PAGE = 1000;

DECLARE @PageNumber AS INT;
SET @PageNumber = 1;

WITH    Data
          AS ( SELECT DISTINCT
                        forgweight.datavalue
               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 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    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'
                        AND ISNUMERIC(ISNULL(forgweight.datavalue, 0)) = 1
             ),
        Ordered
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY D.datavalue ) AS RN ,
                        D.datavalue
               FROM     Data D
             )
    SELECT  O.datavalue ,
            CAST(ISNULL(O.datavalue, 0) AS NUMERIC(15, 5))
    FROM    Ordered O
    WHERE   O.RN BETWEEN ( ( @PageNumber - 1 ) * @ROWS_PER_PAGE + 1 ) AND ( @PageNumber * @ROWS_PER_PAGE )
    ORDER BY O.datavalue;

Open in new window


Do a binary search. Thus start with ROWS_PER_PAGE half the number of distinct rows in the Data CTE. then page through it. When an exception is thrown, half the ROWS_PER_PAGE value and page through the problem page.
Mystical_Ice
ASKER
That script gives me the error: "Incorrect syntax near Ordered"
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ste5an
hmm, should work. Are you really on 2008R2 ?
damey
Use IsNumeric in case statement & I alway use try catch on all sql:
BEGIN TRY
Select
CAST(
       CASE
           WHEN IS NULL Value
           THEN 0
           WHEN LEN(forgweight) = 1 AND ISNUMERIC(VALUE + '.e0') = 1 -- decimal
           THEN VALUE
           WHEN LEN(forgweight) > 1 AND ISNUMERIC(Value + '.0e0') = 1 -- integer
           THEN VALUE
           ELSE 0 END AS as numeric(15,5)) From forgweight
SELECT
-- I wrap the error statement in a stored proc .... See below
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END TRY
BEGIN CATCH
SELECT
-- I wrap the error statement in a stored proc .... See below
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

BUT I would first run the following to do a quick check and find the first row that errors this will give you the rownum

BEGIN TRY
    SELECT CAST(ISNULL(forgweight.datavalue,0) as numeric(15,5)) From forgweight
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    -- Instead of a select I insert this info into a error table
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;


-------------------------------------------------------


-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
GO
damey
Opps... hopefully obvious but you'll have to change "Value" to forgweigh and I would put the case statement into a user funtion or scalar function <-- I prefer scalar.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
damey
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent