sorry, as_scalar() in 0.4. in 0.3, correlate() is not generative
(i.e. modifies the parent select(), returns None), so thats your
problem (call correlate() beforehand).
On Sep 9, 2007, at 4:03 PM, [EMAIL PROTECTED] wrote:
>
> On Sunday 09 September 2007 22:51:32 Michael Bayer wrote:
>> try calling scalar() on that subquery, it needs to be treated as
>> such.
> oops, forgot to mention: this is 0.3.xx.
> in 0.4 all is okay without scalars.
>
> so, 0.3.latest, adding .scalar() after .correlate() complains about
> None having no .scalar attribute; adding .scalar() before
> the .correlate() gives:
> Traceback (most recent call last):
> File "tests/convertertest.py", line 144, in
> test4_balance_trans_via_prev_balance_date_subselect
> b.c.finaldate < balance.c.finaldate
> File "/home/az/src/dbcook/sqlalchemy/sql.py", line 1215, in scalar
> return self.execute(*multiparams, **params).scalar()
> File "/home/az/src/dbcook/sqlalchemy/sql.py", line 1208, in execute
> return self.compile(bind=self.bind,
> parameters=compile_params).execute(*multiparams, **params)
> File "/home/az/src/dbcook/sqlalchemy/sql.py", line 1097, in execute
> raise exceptions.InvalidRequestError("This Compiled object is not
> bound to any Engine or Connection.")
> InvalidRequestError: This Compiled object is not bound to any Engine
> or Connection.
>
> which is true, its all unbound.
>
> btw .scalar() behaves same in 0.4.. so i guess its not that.
>
> if its too much of a hassle, forget it, one testcase less when 0.3
> (-;).
> svilen
>
>> On Sep 9, 2007, at 2:37 PM, [EMAIL PROTECTED] wrote:
>>> g'day.
>>> i have a subselect that may yield null (nothing found), and i
>>> want to treat that as value of 0. i've read about coalesce() that
>>> would return first non-null of its args.
>>>
>>> plain query looks like:
>>>
>>> expr = and_( trans.c.account.startswith( balance.c.account),
>>> trans.c.date) <= balance.c.finaldate,
>>> trans.c.date > select( [ func.max( b.c.finaldate)],
>>> b.c.finaldate <
>>> balance.c.finaldate ).correlate( balance)
>>>
>>> this gives something like:
>>> $print expr
>>> trans.account LIKE balance.account+'%' \
>>> AND trans.date <= balance.finaldate \
>>> AND trans.date > coalesce((SELECT max(b.finaldate)
>>> FROM balance AS b
>>> WHERE b.finaldate < balance.finaldate)
>>>
>>>
>>> adding coalesce breaks the subselect:
>>>
>>> expr = and_( trans.c.account.startswith( balance.c.account),
>>> trans.c.date) <= balance.c.finaldate,
>>> trans.c.date > func.coalesce(
>>> select( [ func.max( b.c.finaldate)],
>>> b.c.finaldate <
>>> balance.c.finaldate ).correlate( balance),
>>> 0 )
>>> $print expr
>>> trans.account LIKE balance.account + '%' \
>>> AND trans.date <= balance.finaldate \
>>> AND trans.date > coalesce(NULL,0)
>>>
>>> removing the correlate() restores proper subselect, but now its
>>> not correlated, and has 'FROM balance as b, balance' in it...
>>>
>>> or should i use CASE instead?
>>> i'm sure i've misunderstood all this sql thing...
>>> (The subselect is just trying to find the date of previous row in
>>> table balance before current balance.finaldate, OR 0.)
>>>
>>> svilen
>>
>>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
