Red Gate forums :: View topic - Bug that is not an Aardvark bug
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Aardvark
Aardvark forum

Bug that is not an Aardvark bug

Search in Aardvark forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
Jason
Guest





PostPosted: Tue Jan 07, 2003 4:17 pm    Post subject: Bug that is not an Aardvark bug Reply with quote

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


Back to top
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu Jan 09, 2003 7:26 pm    Post subject: Re: Bug that is not an Aardvark bug Reply with quote

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


Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group