I've got a query
select distinct
r.max_range,
convert(float,isnull(replace(r.max_range,0.0,100000000.0),100000000.0)) as max_amt,
convert(float,r.max_range) as 'convert_float',
replace(r.max_range,0.0,100000000.0) as 'replace_question'
from #temp t1
join LTR_Amounts r on (isnull(t1.amt,0) >= r.min_range
and isnull(t1.amt,0) <= convert(float,isnull(replace(r.max_range,0.0,100000000.0),100000000.0)))
where r.category_id = 3
and r.inactive <> 'y'
that produces the following - I've got an amount 100,000 and it should fall into the
| max_range | max_amt | convert_float | replace_question |
|---|---|---|---|
| 24999.99 | 25000 | 24999.99 | 25000 |
| 49999.99 | 50000 | 49999.99 | 50000 |
| 99999.99 | 100000 | 99999.99 | 100000 |
| 199999.99 | 200000 | 199999.99 | 200000 |
This can be run as follows
declare @max_range float = 99999.99
select distinct
@max_range, convert(money,isnull(replace(@max_range,0.0,100000000.0),100000000.0)) as max_amt,
convert(money,@max_range) as 'convert_float',
replace(@max_range,0.0,100000000.0) as 'replace_question'
| max_range | max_amt | convert_float | replace_question |
|---|---|---|---|
| 99999.99 | 100000.00 | 99999.99 | 100000 |
The issue at hand is if you can see everywhere as soon as I use replace as part of my query it rounds up the value. If i've got max_range of 99999.99 when I use replace as part of the formula it is looking at it as 1000000 but I need it to keep looking at it as 99999.99
the business rules in place require me to use replace (or some version of that) because sometimes the value is 0.0 and then I need it replaced with some maximum value.
How can I keep my formula as part of my join (with replace)
First off, floats are approximate, i.e. not all values in the data type range can be represented exactly - see here for more info.
Secondly, REPLACE takes character or binary data types as arguments so your query is implicitly converting from an approximate value to a string.
I suggest using an appropriate fixed numeric data type like a decimal and changing out REPLACE for something like a CASE statement.