Databases
StackOverflow2013
Postsdbo
POSQL Execution Error. Error converting data type nvarchar to real
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Execution Error. Error converting data type nvarchar to real
    primarykey
    data
    text
    <p>Presently I am troubleshoot a SQL Server 2008 query that started failing. Here is the query:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>List&lt;String&gt; lstLinesOfFile = new List&lt;string&gt;(); 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++; } </code></pre> <p>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.</p> <p>UPDATE:<br> Here is the code I wrote to verify that it was checking every line of data:</p> <pre><code>List&lt;String&gt; lstLinesOfFile = new List&lt;string&gt;(); 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++; } </code></pre> <p>This gives results like:</p> <pre><code>.... 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 .... </code></pre> <p>UPDATE 2: Pasted code above from the full original view.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser8128167
    1. USuser8128167
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Execution Error. Error converting data type nvarchar to real
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODidn't you ask almost the same question a year ago with the same table? Does the answer you accepted still not help (ie search for rows based on ISNUMERIC)? http://stackoverflow.com/questions/9136722/sql-server-2008-error-converting-data-type-nvarchar-to-float
      singulars
      1. POSQL Execution Error. Error converting data type nvarchar to real
      1. USLouD
    2. COWhen I try using the CASE WHEN like I did there I still get the same error.
      singulars
      1. POSQL Execution Error. Error converting data type nvarchar to real
      1. USuser8128167
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload