tags:

views:

52

answers:

5

I've got a database using DECIMAL type values for various tests on crude oil. When running these tests you run into certain cases: 1.) Test was run and value is found; 2.) You can definitively say nothing is there (0 in database); 3.) Test was not run (NULL in database); 4.) There is something found, but testing is not accurate enough to discern exactly how much (-1 is used in the database for this case).

My problem here is that I need to return result sets as a sum of all 4. I have been using a "SELECT property1+property1+... FROM..." type of query to do this. I use 'WHERE _ IS NOT NULL' to deal with case 3, but the problem is in case 4, I don't want to add -1, I want to add it as 0. Is there any way I can do this with MySQL? I'd really rather not get each result set separately and have to use PHP to deal with this, but I suppose I can if there is no way around it.

Also, before someone recommends it, there isn't too much I can do in the way of changing the database structure. However, if you have some completely ingenious fix that requires that, I wouldn't be opposed to hearing it regardless.

EDIT: Thanks for all the quick replies everybody. Appreciate the help!

A: 

if you do something like ... + cond4 + ... you can replace it with ... + CASE WHEN cond4 == -1 THEN 0 ELSE cond4 END + ...

munissor
+3  A: 

You could use the if statement:

property1 + property2 + property3 + IF(property4 > 0, property4, 0)
ircmaxell
I like this - quite easy to implement it syntactically.
JKB
A: 
SELECT  SUM(GREATEST(COALESCE(property1, 0), 0))
FROM    mytable
Quassnoi
Shouldn't that be `GREATEST` instead of `LEAST`? Otherwise the `-1` will stay `-1`, and a `100` will become `0` (The exact opposite of the desired behavior)...
ircmaxell
@ircmaxell: right, of course.
Quassnoi
A: 

I would consider the Control Flow Functions

SELECT property1 + property2 + property3 + IF(property4 > 0, property4, 0) WHERE _ IS NOT NULL
Ryan Kinal
A: 

I personally wouldn't have this kind of logic executed on the database, it doesn't seem right.

Csharper