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
-~----------~----~----~----~------~----~------~--~---
