common Problem with case...when ?
-
Does anybody know anything smarter than repeating a formula like this:
select
case
when a_real_long_monster_formula_goes_here <= 0
then 0
else a_real_long_monster_formula_goes_here
end
-
Yes, do complex calculations at the client (scnr)
Hm, never tried it myself, but maybe a UDF could help?.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If you can identify a particular column that will cause a negative result then simply multiply your entire formula by
sign(sign(MyNegativeColumn)+1)
The sign function returns
- -1 for negatives
- 0 for 0
- +1 for positives.
This means that the additional clause returns
- 0 for negative numbers
- 1 for any value >=0
If you can't pin down the cause of the negatives then go with Frank's suggestion. Do complicated maths on the client.
-
hm.. underused use of the derived table ... perhaps a jedi's most powerful tool, it is! Use it wisely, you must. greatly shorten complicated expressions, it will.
select
case
when f <= 0
then 0
else f
end
from
(select a_real_long_monster_formula_goes_here as f) tmp
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply