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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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.
