0

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)

YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
  • 2
    `REPLACE` is for **strings/text**, not numbers - your query is performing implicit conversion from numbers to text and back again, which is a nonsense operation. – Dai Feb 08 '23 at 17:06
  • I, personally, would suggest that the real problem is you are using a `float` and treating it like a string. – Thom A Feb 08 '23 at 17:06
  • 1
    _"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."_ - use `CASE` or `NULLIF` - not `REPLACE`. – Dai Feb 08 '23 at 17:06
  • ...also, the "business rules" you describe are presentation-layer concerns and so **should not** be implemented in SQL, instead substituting values for human-readable-display should be done by the part of your application or system that actually renders data on-screen (i.e. your reporting engine). – Dai Feb 08 '23 at 17:08
  • 1
    `convert(float,isnull(replace(r.max_range,0.0,100000000.0),100000000.0)))` <-- Please tell me that this didn't _feel right_ to you when you were writing it... – Dai Feb 08 '23 at 17:10
  • @Dai i definitely have some qualms about this query but using CASE produces a value without the decimal it comes back with 99999 – YelizavetaYR Feb 08 '23 at 17:14
  • @YelizavetaYR Methinks [you need to familiarize yourself with SQL Server's _implicit type conversion_ rules - and how to avoid them](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16). – Dai Feb 08 '23 at 17:20
  • 2
    Also: [**NEVER** represent money/currency values with `float`](https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency) - that's Databases 101 stuff. – Dai Feb 08 '23 at 17:22
  • @Dai the table we are using with the values are built as floats - they are used designed for a variety of purposes so they track the values as decimals/floats. This is one of the many custom add ons being asked for and in this case in comparison to money values so i don't always have a choice – YelizavetaYR Feb 08 '23 at 19:03

1 Answers1

1

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.

Deirdre O'Leary
  • 420
  • 2
  • 6