Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: Return 0 for negative numbers

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       CASE
          WHEN 0 < myCol  THEN myCol
          ELSE 0
       END AS myExpr
       FROM myTable;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    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 View Post
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2004
    Posts
    144
    i found another solution, what do u think of this one:
    Code:
    SELECT GREATEST(fld1, 0) FROM ...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    what do u think of this one
    creative, but not portable to other databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •