Yeah, we'll need a way to alias the column created by the subquery. Have you tried using the "use_labels" switch on the inner query?
Rick
On 5/13/06,
Gambit <[EMAIL PROTECTED]> wrote:
Hey Rick,
I was playing with this patch a bit and noticed that while it seems pretty
good for select()'s, it falls apart in sub-selects. It doesn't propagate as
a column, thus doesn't get an alias for the outer select to work off of, and
it all falls apart :)
Hopefully there's an easy way to solve this :D Test case enclosed! Patch as
you included it in your mail was applied against rev 1453.
Cheers!
-G
On Friday, May 12, 2006, 12:17:18 AM, you wrote:
> Hey Mike:
> 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:
>>
>> 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.5back 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
