Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to real

Question

Presently, I am executing the following query and receiving the above error message:

SELECT dbo.qryOtherFieldDataVerifySource.ItemID,
       dbo.qryOtherFieldDataVerifySource.EDGRDataID,
       dbo.qryOtherFieldDataVerifySource.LineItemID,
       dbo.qryOtherFieldDataVerifySource.ZEGCodeID,
       dbo.qryOtherFieldDataVerifySource.DataValue,
       dbo.tblBC.AcceptableValues,
       dbo.qryOtherFieldDataVerifySource.DataUnitID,
       dbo.qryOtherFieldDataVerifySource.DataDate,
       dbo.tblBC.DataTypeID,
       CASE
         WHEN DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}'
              AND ISNUMERIC(DataValue) = 1 THEN ( CASE
                                                    WHEN CAST(DataValue AS FLOAT(8)) >= 0 THEN 1
                                                    ELSE 0
                                                  END )
         ELSE 0
       END AS ValidPositiveNumericValue,
       CASE DataTypeID
        WHEN '{A6317BA5-F8FB-4866-A26B-24594650C2DC}'THEN ( CASE UPPER(DataValue)
        WHEN 'TRUE' THEN 1
        WHEN 'FALSE' THEN 1
        WHEN 'YES' THEN 1
        WHEN 'NO' THEN 1
        WHEN 'Y' THEN 1
        WHEN 'N' THEN 1
        WHEN '0' THEN 1
        WHEN '1' THEN 1
        ELSE 0
      END )
         WHEN '{5951994B-BF47-4117-805D-B8F85FAB76A8}' THEN ISNUMERIC(DataValue)
         ELSE 1
       END AS ValidDataType,
       dbo.tblZEGCode.ZEGCode,
       dbo.qryOtherFieldDataFieldName.FieldName
FROM   dbo.qryOtherFieldDataVerifySource
       LEFT OUTER JOIN dbo.qryOtherFieldDataFieldName
         ON dbo.qryOtherFieldDataVerifySource.ItemID = dbo.qryOtherFieldDataFieldName.ItemID
       LEFT OUTER JOIN dbo.tblBC
                       RIGHT OUTER JOIN dbo.tblZEGCode
                         ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID
         ON dbo.qryOtherFieldDataVerifySource.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID 

Does anyone have any suggestions?

Answer 1

I suggest looking for the bad value that is preventing you from converting to type float (aka real) with the trick of concatenating e0 to the value before testing it:

SELECT *
FROM dbo.YourTable
WHERE
   DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}' -- the type for float
   AND IsNumeric(
      DataValue + CASE WHEN DataValue NOT LIKE '%[ed]%' THEN 'e0' ELSE '' END
   ) = 0
   AND IsNumeric(DataValue) = 1
;

This works in SQL Server 2000 and up.

UPDATE 1: Since you shared that you want to find only those that can't be detected easily, not all those that aren't truly numeric, I added the second IsNumeric.

UPDATE 2: You finally told me that some of your values already have scientific notation in them. This is quite easily handled. I have updated the query above. Please try it on for size.

To anyone using SQL Server 2012 or higher, this problem is probably best solved with TRY_PARSE:

SELECT TRY_PARSE(Value AS float)

This will convert any values to float that can be, but will return NULL for any others. Thus, you can use this to check if conversion to float will fail by checking to see if this expression IS NULL.

Answer 2

Try this one -

SELECT
      s.ItemID
    , s.EDGRDataID
    , s.LineItemID
    , s.ZEGCodeID
    , s.DataValue
    , t.AcceptableValues
    , s.DataUnitID
    , s.DataDate
    , t.DataTypeID
    , ValidPositiveNumericValue =
        CASE WHEN DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}' AND ISNUMERIC(DataValue) = 1 
            THEN CASE WHEN CAST(DataValue AS FLOAT(8)) >= 0 THEN 1 ELSE 0 END
            ELSE 0
        END
    , ValidDataType =
       CASE 
        WHEN DataTypeID = '{A6317BA5-F8FB-4866-A26B-24594650C2DC}'
            THEN CASE WHEN UPPER(DataValue) IN ('TRUE', 'FALSE', 'YES', 'NO', 'Y', 'N', '0', '1') THEN 1 ELSE 0 END 
        WHEN DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}' 
            THEN ISNUMERIC(DataValue)
         ELSE 1
       END
    , c.ZEGCode
    , f.FieldName
FROM dbo.qryOtherFieldDataVerifySource s
LEFT JOIN dbo.qryOtherFieldDataFieldName f ON s.ItemID = f.ItemID
RIGHT JOIN dbo.tblZEGCode c ON s.ZEGCodeID = c.ZEGCodeID
LEFT JOIN dbo.tblBC t ON t.BCID = c.BCID 

Answer 3

Created the following cursor to cause the query to execute one row at a time, which allowed me to identify the problem data row:

SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF

DECLARE @msg VARCHAR(4096)

BEGIN TRY

    DECLARE @itemid AS NVARCHAR(255);

DECLARE C CURSOR FAST_FORWARD FOR
    SELECT ItemID AS itemid
    FROM dbo.qryOtherFieldDataVerifySource;

OPEN C;

FETCH NEXT FROM C INTO @itemid;

WHILE @@fetch_status = 0
BEGIN

    SELECT  dbo.qryOtherFieldDataVerifySource.ItemID, dbo.qryOtherFieldDataVerifySource.EDGRDataID, dbo.qryOtherFieldDataVerifySource.LineItemID, 
            dbo.qryOtherFieldDataVerifySource.ZEGCodeID, dbo.qryOtherFieldDataVerifySource.DataValue, dbo.tblBC.AcceptableValues, 
            dbo.qryOtherFieldDataVerifySource.DataUnitID, dbo.qryOtherFieldDataVerifySource.DataDate, dbo.tblBC.DataTypeID, 
            CASE WHEN DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}' AND ISNUMERIC(DataValue) = 1 THEN (CASE WHEN CAST(DataValue AS Float(8)) 
            >= 0 THEN 1 ELSE 0 END) ELSE 0 END AS ValidPositiveNumericValue, 
            CASE DataTypeID WHEN '{A6317BA5-F8FB-4866-A26B-24594650C2DC}' THEN (CASE UPPER(DataValue) 
            WHEN 'TRUE' THEN 1 WHEN 'FALSE' THEN 1 WHEN 'YES' THEN 1 WHEN 'NO' THEN 1 WHEN 'Y' THEN 1 WHEN 'N' THEN 1 WHEN '0' THEN 1 WHEN '1' THEN 1 ELSE
            0 END) WHEN '{5951994B-BF47-4117-805D-B8F85FAB76A8}' THEN ISNUMERIC(DataValue) ELSE 1 END AS ValidDataType, dbo.tblZEGCode.ZEGCode, 
            dbo.qryOtherFieldDataFieldName.FieldName
     FROM   dbo.qryOtherFieldDataVerifySource LEFT OUTER JOIN
            dbo.qryOtherFieldDataFieldName ON dbo.qryOtherFieldDataVerifySource.ItemID = dbo.qryOtherFieldDataFieldName.ItemID LEFT OUTER JOIN
            dbo.tblBC RIGHT OUTER JOIN
            dbo.tblZEGCode ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID ON dbo.qryOtherFieldDataVerifySource.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID
     WHERE dbo.qryOtherFieldDataVerifySource.ItemID = '' + @itemid + ''

    FETCH NEXT FROM C INTO @itemid;
END

CLOSE C;

DEALLOCATE C;

END TRY 
BEGIN CATCH
SET @msg = 'Failure occurred attempting to execute qryOtherFieldDataVerify.';
EXEC ApplicationManagement.dbo.spLogProcedureCall 
    @ObjectID       = @@PROCID,
    @AdditionalInfo = @msg;
RAISERROR(@msg, 0, 1);
END CATCH   

DECLARE @returnCode int
SET @returnCode = CASE WHEN @msg IS NOT NULL THEN 1 ELSE 0 END
SELECT @returnCode AS ErrCode;