Return 0 for negative numbers

bono56 · #1 (**permalink**) 01-30-12, 17:06

Hi
How can i retrun 0 for negative numbers in a SELECT statement?

Pat Phelan · #2 (**permalink**) 01-30-12, 17:20

Code:

SELECT
   CASE
      WHEN 0 < myCol  THEN myCol
      ELSE 0
   END AS myExpr
   FROM myTable;

-PatP

Pat Phelan · #3 (**permalink**) 01-30-12, 17:24

The coding style is a little bit funky, but it works around a number of bugs in MySQL and how some versions and storage engines within it processes CASE statements.

You can code the statement other ways, but be VERY careful that they do what you want in your environment. I haven't found any MySQL environment yet that won't process this syntax the way I expect it to.

-PatP

r937 · #4 (**permalink**) 01-30-12, 22:29

Quote:

Originally Posted by Pat Phelan

The coding style is a little bit funky, but it works around a number of bugs in MySQL and how some versions and storage engines within it processes CASE statements.

what?

oh, please, do tell

i've been using CASE expressions for years, and ~never~ ran into even one bug, let alone "a number"

or is this just another instance of you microsoft fanboys slamming mysql again?

Pat Phelan · #5 (**permalink**) 01-31-12, 13:36

Quote:

Originally Posted by r937

what?

oh, please, do tell

Check the MySQL release notes for examples. Contrary to what you might think, MySQL is written/maintained/installed/used by human beings. It has bugs just like everything else.

I'm pretty sure that you live in a cherry-picked world where you install a clean, stable version of MySQL and use it for years. I use MySQL in production environments where there may be different builds on every server and there are bugs and idiocyncracies galore. We work in very different environments, so I'm not surprised that we have different experiences and expectations.

Quote:

Originally Posted by r937

or is this just another instance of you microsoft fanboys slamming mysql again?

Oh yeah, I'm a real fanboy! You're cute when you're so silly!

-PatP

r937 · #6 (**permalink**) 01-31-12, 13:44

touched a nerve, did i?

you still haven't revealed any problems with the CASE expression

but you're right, i work only with stable releases

i'm sure that unstable releases of squeal server are just as, um, unstable...