Cannot do a reliable convert in the where clause for comparison

I'm dealing with this custom database where an attribute for an authorization can store many things (amount approved, flags, yes/no, etc.)

For the attribute that store the amount approved, I have a job that starts off periodically to put a TermDate on it based on some business rules.  I had an expression like this in the where clause:  CONVERT(MONEY, @value, 1) > 10000 simply because @value comes in as a VARCHAR field.

This fails, which I can understand.  it's doing a table scan and failed when converting a "YES" into MONEY.

I then built a subquery to filter out the data first and convert @value into MONEY first.  The subquery works fine and brings back data the way I want.  But when I substitute this subquery in place of the original table I'm using and try to compare the "converted" value, it still didn't work.

The workaround I have right now is to put the subquery result into a temp table and select out of the temp table to do my compare.  This part is not that intuitive to me and I'll find out why and post a reply here.

Feel free to post your 2 cents.

Notice that helpful hint from Chris Alcock below that I forgot to mention that I tried.

 

 

 

[Update 2009-11-04 by Chris Alcock]

you may want to try the having your where clause handle the non-numeric cases for you, something like:

where case when isnumeric(@value) = 1 then convert(MONEY, @value, 1) else 0 end > 10000

this will cause non-numeric values to be converted to 0, but you could equally use null or some other 'magic number'

Hope that helps,

Chris

Print | posted on Tuesday, November 3, 2009 6:09 PM

Feedback

# re: Cannot do a reliable convert in the where clause for comparison

Left by Chris Alcock at 11/4/2009 1:19 AM
Gravatar you may want to try the having your where clause handle the non-numeric cases for you, something like:

where case when isnumeric(@value) = 1 then convert(MONEY, @value, 1) else 0 end > 10000

this will cause non-numeric values to be converted to 0, but you could equally use null or some other 'magic number'

Hope that helps,

Chris

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski