Bug that is not an Aardvark bug

Brian Donahue

Hello again Jason,

This problem will also be fixed in version 2.0. It was fixed by
prefacing the value with a full-stop (.).
When you do this in Query Analyzer: SELECT ISNUMERIC('$'), you will get a 1
(TRUE).
Now try this one: SELECT ISNUMERIC('.$') and you get a 0 (FALSE).
SELECT ISNUMERIC('.1.') will correctly yeild a 1 again.

Regards,

Brian Donahue

Technical Support Engineer

Red Gate Software Ltd.

T: +44 870 1600 037

E: mailto:brian.donahue@red-gate.com



"Jason" <jspeight@advaoptical.com> wrote in message
news:dvuq1DmtCHA.1348@server53...
> Hi,
>
> We have had a problem with Aardvark for a while, and I have only worked
out
> why this issue was occuring.
>
> I have discovered that you use the isnumeric function before you use the
> convert function. This is an example query:
>
> SELECT CustomFields.*, CustomFieldValues.[Value] AS FieldValue,
> CustomFieldComboEntries.[Value] AS ComboValue
> FROM CustomFields LEFT OUTER JOIN
> CustomFieldValues ON CustomFields.RecordID =
> CustomFieldValues.CustomFieldID AND CustomFieldValues.BugID = 212 LEFT
OUTER
> JOIN
> CustomFieldComboEntries ON
> CustomFieldComboEntries.CustomFieldID = CustomFieldValues.CustomFieldID
AND
> CustomFieldComboEntries.RecordID = CASE WHEN
> ISNUMERIC(CustomFieldValues.Value) = 1 THEN CONVERT(float,
> CustomFieldValues.Value)
> ELSE - 1 END
> WHERE (CustomFields.CompanyID = 3)
> ORDER BY CustomFields.Sequence
>
> This is flawed, however. IsNumeric will pass a single hyphen character as
> being numeric, however, this will fail the convert to float conversion. I
> can only assume this affects other characters.
>
> Thanks,
>
> Jason
> jasons.at.advaoptical.com
>
>