try calling scalar() on that subquery, it needs to be treated as such.
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 -~----------~----~----~----~------~----~------~--~---
