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