February 9, 2009 at 7:44 am
Hello
I have a database created for scientific data (water quality, air quality monitoring,ect...). From our dataloggers we get a raw lysimeter flow value (stored in [param_value]). Depending on the date (call it a switch-over date, when the design of the lysimeter was changed) and location of the lysimeter, I need to correct this stored value to a corrected value that gives flow in inches of water. I have the following CASE statement:
'param_value' = CASE
WHEN (l.sys_loc_code = 'TP-4' AND (sample_date < '4/16/2008'))
THEN (CAST(param_value AS real)*((POWER(8,2))/(POWER(94,2))))
WHEN (l.sys_loc_code = 'TP-4' AND (sample_date >= '4/16/2008'))
THEN (CAST(param_value AS real)*((POWER(6.5,2))/(POWER(94,2))))
WHEN (l.sys_loc_code IN ('TP-8','TP-9','TP-10','TP-11') AND (sample_date < '4/16/2008'))
THEN (CAST(param_value AS real)*((POWER(8,2))/(POWER(48,2))))
WHEN (l.sys_loc_code IN ('TP-8','TP-9','TP-10','TP-11') AND (sample_date >= '4/16/2008'))
THEN (CAST(param_value AS real)*((POWER(6.5,2))/(POWER(48,2))))
WHEN (l.sys_loc_code = 'TP-5' AND (sample_date < '8/14/2006'))
THEN (CAST(param_value AS real)*((POWER(8,2))/(POWER(94,2))))
WHEN (l.sys_loc_code = 'TP-5' AND (sample_date >= '8/14/2006'))
THEN (CAST(param_value AS real)*((POWER(6.5,2))/(POWER(94,2))))
ELSE CAST(param_value AS real)
END,
No matter what the order of the statements, or how I insert parentheses, I only get the correctly-evaluated expression for where the conditions are that sample_date is >= the switch-over date. When I say location is equal to X and date is less than the switch-over date, the expression results in a zero value.
Other things that may be relevant:
[param_code] is a varchar, and hence I converted it to a numeric data type - I don't know if this is necessary.
Anyway, any guidance as to what I am doing wrong would be helpful. I have check around other parts of my query, and have satisfied myself that this is where the issue is.
Thanks,
Sanjay
February 9, 2009 at 7:57 am
select (POWER(8,2))/(POWER(94,2))
Returns 0, which means you are multiplying by 0, which will get 0.
Try:
select (POWER(8.0,2))/(POWER(94.0,2))
And you get 0.007243.
Try adding ".0" to those parts of your Case statement, see if that gets you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 8:08 am
GSquared -
That worked! So easy. Thanks a bunch.
SA
February 9, 2009 at 8:24 am
Just a matter of knowing how implicit conversion works. Watch out for integer division. In SQL Server, when you divide an integer by a number larger than it, it returns 0. Have to force it to use float or decimal or some such to avoid that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 8:27 am
Yes! It is those kinds of things that I just haven't learned yet (the fun and the pit falls of coming to databases from another field...)
Best
February 9, 2009 at 11:31 am
Welcome to SQL. Like anything else, it certainly has its quirks. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply