Bug that is not an Aardvark bug

[Jason]

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

[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
>
>



_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521