No problem. Better report and check that not check and leave a bug in
there.
Can you explain the fix below? Oracle does not understand AS? Is there
documentation about this somewhere I can reference as a comment to the
fix?
Massimo
On Apr 16, 1:11 am, Alexey Nezhdanov <snak...@gmail.com> wrote:
> Oh, Massimo, actually I was wrong here again. Sorry for second time
> reporting same issue while still not being a bug.
> However, I still catched one, related specifically to Oracle.
> As it happened, using LEFT JOIN here is more correct as I need empty groups
> too. But Oracle chockes with error ORA-00905 if there is a keyword 'AS' in
> sql. So the following patch made it work for me:
> --- sql.py 2010-03-11 18:18:38.000000000 +0300
> +++ sql.py 2010-04-16 10:04:34.087553445 +0400
> @@ -1586,7 +1590,10 @@
>
> def __str__(self):
> if self.get('_ot', None):
> - return '%s AS %s' % (self._ot, self._tablename)
> + if self._db._dbname == 'oracle':
> + return '%s %s' % (self._ot, self._tablename)
> + else:
> + return '%s AS %s' % (self._ot, self._tablename)
> return self._tablename
>
> def with_alias(self, alias):
>
> Regards
> Alexey
>
> On Thu, Apr 15, 2010 at 5:40 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > Alias was not designed to let you rename tables in arbitrary cases. It
> > was designed to prevent naming conflicts when you left join the same
> > table multiple times. It works in this second case. So this should
> > work
>
> > db(db.TEST.id>0). select(TEST.ALL,
> > b.id.count(),left=b.on(b.top_id==TEST.id))
>
> > I agree this is not completely equivalent to your query.
>
> > Massimo
>
> > On Apr 15, 3:07 am, Alexey Nezhdanov <snak...@gmail.com> wrote:
> > > Ok, I bumped into same problem again.
> > > But this time I actually DO use same table twice.
>
> > > So, the task: there is a table that MAY refer to itself.
> > > In my application monitors network tree for disconnections (is just pings
> > > hosts). Some alerts 'come out of the blue' and some are caused by these -
> > > i.e. we have cascade effects:
> > > monitoring_app--A--B--C
> > > if A is the server and B fails - then there will be two alerts: 'B
> > > inaccessible' and 'C inaccessible' but record for node C will actually
> > refer
> > > to record
> > > for node B because C probably is ok, we just can't check it.
>
> > > I need to fetch all 'out of the blue' alert records AND number of
> > associated
> > > alerts for each.
> > > correct SQL will be something like
> > > select a.id, count(b.id) from TEST a, TEST b where b.top_id=a.id and
> > a.id=0
> > > group by a.id
> > > However web2py losts table names in the process causing DB error.
> > > (stripped everything down so below is all that is needed to reproduce the
> > > problem. groupby is not needed so stripped out too)
> > > =======models/db.py=========
> > > db = DAL('sqlite://storage.sqlite')
> > > db.define_table('TEST',Field('top_id','integer'))
> > > ======controllers/default.py=====
> > > def index():
> > > a=db.TEST.with_alias('a')
> > > b=db.TEST.with_alias('b')
> > > print db((a.id==0)&(b.top_id==a.id))._select(a.ALL, b.id.count())
> > > print db((a.id==0)&(b.top_id==a.id)). select(a.ALL, b.id.count())
> > > return dict(message=T('Hello World'))
> > > ==========================
>
> > > The question is - is there a workaround (except obvious - two TWO sql
> > > queries) or may be I am just not using aliases in the correct way again?
>
> > > Regards
> > > Alexey.
>
> > --
> > To unsubscribe, reply using "remove me" as the subject.
