StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So starting with your update query that's giving an error (please forgive me for rewriting it for my own clarity):</p> <pre><code>UPDATE B SET OriginalValue = DataValue, OriginalUnitID = DataUnitID, DataValue = CAST(DataValue AS float) * 1.335 FROM dbo.tblBenchmarkData B INNER JOIN dbo.tblZEGCode Z ON B.ZEGCodeID = Z.ZEGCodeID WHERE B.FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' AND ( Z.ZEGCode = 'C004' OR Z.ZEGParentCode LIKE 'C004%' ) </code></pre> <p>I think you'll find that a SELECT statement with exactly the same expressions will give the same error:</p> <pre><code>SELECT OriginalValue, DataValue NewOriginalValue, OriginalUnitID, DataUnitID OriginalUnitID, DataValue, CAST(DataValue AS float) * 1.335 NewDataValue FROM dbo.tblBenchmarkData B INNER JOIN dbo.tblZEGCode Z ON B.ZEGCodeID = Z.ZEGCodeID WHERE B.FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' AND ( Z.ZEGCode = 'C004' OR Z.ZEGParentCode LIKE 'C004%' ) </code></pre> <p>This should show you the rows that can't convert:</p> <pre><code>SELECT B.* FROM dbo.tblBenchmarkData B INNER JOIN dbo.tblZEGCode Z ON B.ZEGCodeID = Z.ZEGCodeID WHERE B.FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' AND ( Z.ZEGCode = 'C004' OR Z.ZEGParentCode LIKE 'C004%' ) AND IsNumeric(DataValue) = 0 -- AND IsNumeric(DataValue + 'E0') = 0 -- try this if the prior doesn't work </code></pre> <p>The trick in the last commented line is to tack on things to the string to force only valid numbers to be numeric. For example, if you wanted only integers, <code>IsNumeric(DataValue + '.0E0') = 0</code> would show you those that aren't.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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