英文:
Replace Function is rounding Up value in SQL
问题
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)
英文:
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)
答案1
得分: 1
首先，浮点数是近似值，即数据类型范围中并非所有值都可以精确表示 - 更多信息请参见这里。
其次，REPLACE 接受字符或二进制数据类型作为参数，因此您的查询会隐式地将近似值转换为字符串。
我建议使用适当的固定数值数据类型，如decimal，并将 REPLACE 更改为类似 CASE 语句的东西。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论