I'd rather not fix this in sql but fix in dal if that's ok. You have solution for now.
On Feb 19, 7:48 am, Alexey Nezhdanov <[email protected]> wrote: > Hello. > I'm trying to use 'exists' keyword from the DB2 database. I know that web2py > doesn't support that natively so I tried to pass the additional condition in > as a string. > However I struck a problem that the same table used in the same query 3 > times (query greatly simplified here but it still has the problem): > > select NAS.ip from NAS n, SERVICE s where s.intval=n.id and exists (select > s1.id from service s1,service s2 where s.par_id=s1.par_id and > s1.par_id=s2.par_id s1.dict=123 and s2.dict=456); > > so I came out with the web2py Set like this: > > q = (db2.SERVICE.intval==db2.NAS.id)&("exists (select s1.id from service > s1,service s2 where SERVICE.par_id=s1.par_id and s1.par_id=s2.par_id > s1.dict=123 and s2.dict=456)") > result=db2(q).select(db2.NAS.ip) > > However it fails with the KeyError: 's1' due to parsing the string and > finding s1.something and s2.something and collecting s1 and s2 as > tablenames. > Leaving aside questioning the need for _parsing_generated_string_ I came out > with the following fix to sql.py: > > def parse_tablenames(text): > text = regex_quotes.sub('', text) > while 1: > i = text.find('IN (SELECT ') > + if i == -1: > + i = text.find('EXISTS (SELECT ') > if i == -1: > break > ... > and slightly modifying the Set by making two words uppercase: > q = (db2.SERVICE.intval==db2.NAS.id)&("EXISTS (SELECT s1.id from service > s1,service s2 where SERVICE.par_id=s1.par_id and s1.par_id=s2.par_id > s1.dict=123 and s2.dict=456)") > > -- > Regards > Alexey -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
