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