Attached please find a patch against trunk r#1441 that implements the SQL CASE construct.
I know you've argued in the past for an implementation in some kind of .ext-land, but let me at least present a few arguments for inclusion in the core library. I'll do this in the form of responses to some points in your latest post on this subject way back on St. Patrick's day (begora):
On 3/17/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
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.
...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.
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
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! :-)
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
SQL-CASE.patch
Description: Binary data
