[sqlalchemy] Re: avoid a subselect yielding null
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: avoid a subselect yielding null
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: avoid a subselect yielding null
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: avoid a subselect yielding null
On Sunday 09 September 2007 23:30:20 Michael Bayer wrote: 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). yeah that's it. thanks. now back to that argument, months ago: - how to make same code work with generative and non-generative api? now i need to do: sel = select(...) xx = sel.correlate(..) if not _v03: sel = xx ...use-the-sel... this now is just one place, big deal. But in some wider usage... Anyway, food for thought... and/or documentation. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: avoid a subselect yielding null
On Sep 9, 2007, at 4:49 PM, [EMAIL PROTECTED] wrote: now back to that argument, months ago: - how to make same code work with generative and non-generative api? now i need to do: sel = select(...) xx = sel.correlate(..) if not _v03: sel = xx ...use-the-sel... this now is just one place, big deal. But in some wider usage... Anyway, food for thought... and/or documentation. i dunno, make a function correlate() ? def correlate(select, values): s = select.correlate(values) return s or select --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
