Hi!
On Tue, Dec 23, 2008 at 10:12 PM, Brian Aker <[email protected]> wrote:
> When I have seen it used when an SP should have been written (which really
> meant in most cases the application should have dealt with it). It also made
> the SQL not very portable.
Simple example why you need CASE: ORDER BY where the order is not
derivable from the actual column values.
Take sakila.film. Column rating in MySQL is
rating | enum('G','PG','PG-13','R','NC-17')
In drizzle, exit Enum. Now we want to get a list of films with
ascending rating, meaning, ascending as in having innocent content to
very much restricted content.
In mysql we could get away with
ORDER BY rating,
because the enum doubled as an integer. But in drizzle, the order would be:
G, NC-17, PG, PG-13, R
So, enter CASE:
ORDER BY
CASE rating
WHEN 'G' THEN 1
WHEN 'PG' THEN 2
WHEN 'PG-13' THEN 3
WHEN 'R' THEN 4
WHEN 'NC-17' THEN 5
ELSE 6
END
Or is this something you would rather see handled in the application?
I mean, really - *that* would in my mind be a real portability
nightmare. Really - CASE syntax is supported in any major database
product (at least for the past 5 years). IF on the other hand is MySQL
specific, and not implemented in products like MS SQL and Oracle.
Really, if portability is an issue, strike out IF, not CASE.
Roland.
>
> Cheers,
> -Brian
>
> --
> _______________________________________________________
> Brian "Krow" Aker, brian at tangent.org
> Seattle, Washington
> http://krow.net/ <-- Me
> http://tangent.org/ <-- Software
> _______________________________________________________
> You can't grep a dead tree.
>
>
>
>
--
Roland Bouman
http://rpbouman.blogspot.com/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
