CODEWITHSUNDEEP
c#sql
user8128167
user8128167

Reputation: 7686

SQL Execution Error. Error converting data type nvarchar to real

Presently I am troubleshoot a SQL Server 2008 query that started failing. Here is the query:

SELECT     TOP (100) PERCENT dbo.tblBenchmarkData.FieldDataSetID, dbo.tblBC.BCID, CAST(dbo.tblBenchmarkData.DataValue AS float(8)) AS DataValue, 
                  dbo.tblBC.BCMnemonic, dbo.tblDataType.DataTypeMnemonic
FROM         dbo.tblFieldDataSet RIGHT OUTER JOIN
                  dbo.tblBenchmarkData ON dbo.tblFieldDataSet.FieldDataSetID = dbo.tblBenchmarkData.FieldDataSetID LEFT OUTER JOIN
                  dbo.tblBC LEFT OUTER JOIN
                  dbo.tblDataType ON dbo.tblBC.DataTypeID = dbo.tblDataType.DataTypeID RIGHT OUTER JOIN
                  dbo.tblZEGCode ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID ON dbo.tblBenchmarkData.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID
WHERE     (dbo.tblDataType.DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}') AND (dbo.tblFieldDataSet.OriginalFieldDataSetID IS NULL) AND 
                  (dbo.tblFieldDataSet.Duplicate = 0)
ORDER BY dbo.tblBC.BCMnemonic, DataValue

When I remove the cast and execute the query it returns about 1 400 000 rows, so I take the DataValue results and run a small C# program against this output to verify that all the data is in fact numeric:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   intLineCounter++;
}

The program indicates that there are no data rows that are not numeric, so does anyone have any suggestions as to why I would be receiving this error? TIA.

UPDATE:
Here is the code I wrote to verify that it was checking every line of data:

List<String> lstLinesOfFile = new List<string>();
Int64 intLineCounter = 0;
ReadFile("data.txt");
double dblNum;

foreach (string strValue in lstLinesOfFile)
{
   bool isNum = double.TryParse(strValue, out dblNum);

   if (!isNum)
   {
      Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Value = " + strValue);
   }
   else
   {
       Debug.WriteLine("Line: " + Convert.ToString(intLineCounter) + ", Number = " + strValue);
   }
   intLineCounter++;
}

This gives results like:

....

Line: 241564, Number = 1843.2
Line: 241565, Number = 18430
Line: 241566, Number = 18430.9
Line: 241567, Number = 18431.6
Line: 241568, Number = 18433.9
Line: 241569, Number = 1844.52

....

UPDATE 2: Pasted code above from the full original view.

Upvotes: 1

Views: 7683

Answers (5)

user8128167
user8128167

Reputation: 7686

The above SELECT TOP (100) PERCENT dbo.tblBenchmarkData.FieldDataSetID, dbo.tblBC.BCID... is actually a view called qryFieldParameterBCNumericLookup. When I execute this command:

SELECT TOP 100 * FROM MyDatabase.dbo.qryFieldParameterBCNumericLookup

I get the error:

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

When I execute any of these commands, they work perfectly fine with no errors:

SELECT * FROM MyDatabase.dbo.qryFieldParameterBCNumericLookup
SELECT TOP 50 * FROM MyDatabase.dbo.qryFieldParameterBCNumericLookup
SELECT TOP 1000 * FROM MyDatabase.dbo.qryFieldParameterBCNumericLookup

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56755

This problem occurs because there are a surprising number of odd strings that will pass the ISNUMERIC() test but that will fail to CAST() to float (and may still be convertible by double.TryParse). Three that I know of offhand are:

  • '+'
  • '.'
  • '-'

Here's one way to catch some of these:

SELECT DataValue
FROM   dbo.tblBenchData
WHERE  ISNUMERIC('0'+DataValue) = 0

And strangely, this will either correctly convert or else reject most of them without error:

SELECT CAST('0'+DataValue As float(8)) As DataValue
FROM   dbo.tblBenchData
WHERE  ISNUMERIC('0'+DataValue) = 0

There's still a few odd cases that can get through though.

OK, I had forgotten that this trick does not like valid negative values.

And, as for the "other" odd cases, I really cannot remember them all, nor have I ever seen them listed anywhere (really annoying, I know). However, most of the problem strings come from how the various different numeric parsers view so-called "degenerate cases", that is, single character strings.

So one way to try to address both of these problems would be to pre-check for strings whose length is one, and then handle them specially. So, changing you original query to something like this should work:

;WITH cteBench As
(
    SELECT  *
        ,   CASE
                WHEN DataValue IN('-','.','+')  THEN '0'
                WHEN Len(DataValue) = 1 AND DataValue BETWEEN '0' And '9'
                                                THEN DataValue
                WHEN Len(DataValue) = 1         THEN ''
                WHEN DataValue LIKE '-%'        THEN DataValue
                ELSE                    '0'+DataValue
            END As FixedDataValue
    FROM    dbo.tblBenchmarkData.DataValue
)
SELECT     TOP (100) PERCENT cteBench.FieldDataSetID, dbo.tblBC.BCID, CAST(cteBench.FixedDataValue AS float(8)) AS DataValue, 
                  dbo.tblBC.BCMnemonic, dbo.tblDataType.DataTypeMnemonic
FROM         dbo.tblFieldDataSet RIGHT OUTER JOIN
                  cteBench ON dbo.tblFieldDataSet.FieldDataSetID = cteBench.FieldDataSetID LEFT OUTER JOIN
                  dbo.tblBC LEFT OUTER JOIN
                  dbo.tblDataType ON dbo.tblBC.DataTypeID = dbo.tblDataType.DataTypeID RIGHT OUTER JOIN
                  dbo.tblZEGCode ON dbo.tblBC.BCID = dbo.tblZEGCode.BCID ON cteBench.ZEGCodeID = dbo.tblZEGCode.ZEGCodeID
WHERE     (dbo.tblDataType.DataTypeID = '{5951994B-BF47-4117-805D-B8F85FAB76A8}')
 AND      (dbo.tblFieldDataSet.OriginalFieldDataSetID IS NULL)
 AND      (dbo.tblFieldDataSet.Duplicate = 0)
 AND      ISNUMERIC(FixedDataValue) = 1
ORDER BY dbo.tblBC.BCMnemonic, DataValue

Upvotes: 2

ChrisLively
ChrisLively

Reputation: 88072

You have a right outer and left outer join syntax in that query; but you are using a where clause on the right/left joined tables. That's kind of nasty and probably not doing what you expect.

I'd start with seeing what DataSetID is coming back as null in the tblBenchData table.

Something like: 

SELECT *
  FROM tblDataSet
  WHERE DataSetID NOT EXISTS IN (SELECT DataSetID FROM tblBenchData)

Next, I'm guessing you missed something on your copy/paste because there is no ON condition for the left outer join on tblBenchCode. Could you provide the actual query in use?

Side note: I'm not entirely certain why you have the TOP(100) PERCENT piece. I know it was an unreliable trick to allow order by in view definitions, but it's meaningless today and certainly meaningless in the query you provided... unless this is an ancient SQL server.

Another possibility is, as Loud pointed out, your question from a year ago indicated you had values that were in scientific notation...

Upvotes: 1

Filip
Filip

Reputation: 2347

Use this to figure out which values cannot be parsed by SQL

select * from tblBenchData where ISNUMERIC(DataValue) = 0

or this

select * from tblBenchData where DataValue like '%[a-Z]%'

My guess would be a culture issue (e.g. "," vs ".")

Upvotes: 2

Lee Mandeville
Lee Mandeville

Reputation: 96

At first glance it looks like you are not hooking up the c# code to the sql query.

The line below sets 1stLinesOffline to a empty list of string but you never set it to anything else so the for loop will never run.

List lstLinesOfFile = new List();

I'd expect to see

List 1stLinesOfFile = mySqlResults;

where mySqlResults is the result dataset of your SQL.

Upvotes: 0

Related Questions