Hmm. I do not know about if there is a documentation. Probable there is some
but I just do not know about this.
I used just 'try and fail' approach:
sn...@nezhdanov:~/VTC/pinger/elixir$ ./console.py
select PINGER_RESULTS.id from PINGER_RESULTS left outer join PINGER_RESULTS
pr1 on PINGER_RESULTS.id=pr1.top_problem_id where PINGER_RESULTS.id>3000000;
[('ID', <type 'cx_Oracle.NUMBER'>, 127, 22, 0, -127, 0)]
select PINGER_RESULTS.id from PINGER_RESULTS left outer join PINGER_RESULTS
as pr1 on PINGER_RESULTS.id=pr1.top_problem_id where
PINGER_RESULTS.id>3000000;
ORA-00905: отсутствует ключевое слово
Note that the error is opposite. 00905 is 'missing keyword' where as
_removing_ helps situation (though may be there is something more that can
be added).
On Fri, Apr 16, 2010 at 10:24 AM, mdipierro <[email protected]> wrote:
> 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.
>
