Transforming values

[nimzov]

Hello.

Not sure if this is a math or computer question.

I am scanning a table and I want to write a formula that will return a value of -1, 0 or 1 for each row depending on the value in the row.

Using a combination of the following mathematical mysql functions, I would like to return a value of :

1 if the content of the field is > 0.5
-1 if the content of the field is < 0.5 and
0 if the content of the field is > -0.5 and < 0.5

This is SQL so I am more or less limited with a combination of these functions:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html

http://dev.mysql.com/doc/refman/5.0/en/numeric-functions.html

Thanks.

nimzo

 

[six7s]

You might want to use a CASE STATEMENT (or three IF STATEMENTs) with a STORED PROCEDURE (and maybe(?) a TRIGGER)

 

[iMaGiNaTioN]

if (($content > -0.5) && ($content < 0.5))
{
return 0;
}
else if ($content < 0.5)
{
return -1;
}
else if ($content > 0.5)
{
return 1;
}

 

[nimzov]

I could use stored procedures as suggested by six7s but I
prefer not to go that way if possible.

I found the SQL syntax.

SELECT
CASE
when value > -0.5 AND value < 0.5 THEN 0
when value >= 0.5 THEN 1
when value <= -0.5 THEN -1
END
FROM table
WHERE etc...

Thanks for the suggestions.

nimzo

 

[Myriad]

You can do it without cases or if structures if you want. Comparison operators return values of 1 and 0 that can be used in calculations. Thus:

return ( 0 + (value > 0.5) - (value < 0.5) )

Respectfully,
Myriad

 

[six7s]

You can do it without cases or if structures if you want. Comparison operators return values of 1 and 0 that can be used in calculations. Thus:

return ( 0 + (value > 0.5) - (value < 0.5) )

And then a comment along the lines of

/*
Comparison operators return values of 1 and 0 that can be used in calculations.

Thus:

when value > -0.5 AND value < 0.5 THEN 0
when value >= 0.5 THEN 1
when value <= -0.5 THEN -1​

*/​

 

[nimzov]

Thanks Myriad your solution is much simpler and elegant.

SELECT
((value > 0.5) - (value < -0.5))
FROM table
WHERE etc...