On 3/17/06, Michael Bayer <[EMAIL PROTECTED]> wrote: I love the CASE statement, but yah I think its a cleaner in a view
Well, only if the condition and result expressions used are invariant. If that's the case, you may as well use a simple lookup table in a join. In my particular use case, the condition and result expressions used in the WHEN clauses change on almost every single query.
(also what DB's support this one ? let me guess, PG and Oracle and thats it....oh and MSSQL-9.nobodyhasit :)
...actually CASE is pretty well supported across the board, PG, Oracle, SQLite, MySQL all support it, and MS-SQL has had it since like version 6.5 back in 1996.
). You can use literal strings too for this kind of thing.
Sure but that's effective only if the expressions are rather trivial. Not having to build out string representations of SQL is one of the reasons I use SA in the first place! :-)
if you have some brilliant notion of how this would even look as Python expressions, that would be interesting to see. It would definitely live as a plugged-in extension. I should make an effort to formally define the "extensions" idea I have so that people can contribute whatever plugins they want.
Well, I am certainly not claiming brilliance, but I will stand up for "stupid simple". The meat of the patch is only about 10 lines long.
Here's a short doc:
case(whenlist, [value=value-expr], [else_=else-expr])
whenlist: A list of pairs.
Each pair is itself a sequence or iterator yielding a sequence
of two elements.
Each two-element pair represents a "WHEN" block of an SQL
CASE _expression_. The first element of the pair is an _expression_
that represents the WHEN condition, and the second is an
_expression_ that represents the THEN result.
The optional [value] _expression_, if present, sets up a
"simple case" SQL clause. If present, then each condition
in the whenlist must evaluate to a constant value to which the
result of the value-expr is compared.
The optional [else_] _expression_ represents the optional "ELSE"
clause in the SQL CASE construct.
Some examples:
case([[MyTable.c.MyColumn < 2, 'First Bucket'], [MyTable.c.MyColumn >= 2, 'Second Bucket']])
yields:
CASE WHEN MyTable.MyColumn < 2 THEN 'First Bucket'
WHEN MyTable.MyColumn >= 2 THEN 'Second Bucket'
END
-------------------------------------
case([(100, 'one hundred'), (5, 'five'), (82, 'quatre-vingts deux')],
value=MyTable.c.MyColumn,
else_='some other number')
yields:
CASE MyTable.MyColumn
WHEN 100 THEN 'one hundred'
WHEN 5 THEN 'five'
WHEN 82 THEN 'quatre-vingts deux'
ELSE 'some other number'
END
OK, this doc isn't really correct, as proper literal string quoting requires the literal() function, but you get the idea.
CASE is a pretty basic construct, and I think it belongs right up there with our friends DISTINCT and OUTER JOIN in the main library. Can ya spare a guy space for 10 lines of code?
Thanks,
Rick
