Odd. Try db((db.HourType.id==db.WoTtHt.hourtype)&(db.WoTtHt.workorder==2)& (db.WoTtHt.valid==True)).select( db.HourType.code,db.TaskType.code,left=db.TaskType.on (db.WoTtHt.tasktype== db.TaskType.id))
On Jun 2, 9:42 am, Mariano Mara <[email protected]> wrote: > 2009/6/2 mdipierro <[email protected]> > > > > > I think you want: > > > op1 = db((db.WoTtHt.hourtype==db.HourType.id)&(db.WoTtHt.workorder==2) > > & > > (db.WoTtHt.valid==True)).select( > > db.HourType.code,db.TaskType.code,left=db.TaskType.on > > (db.WoTtHt.tasktype==db.TaskType.id)) > > > Massimo > > Hi Massimo, thanks for your reply. > I already tried that option, the thing is that I hit an error with it. > > From ipython with models imported: > > In [25]: op1 = db((db.WoTtHt.hourtype==db.HourType.id > )&(db.WoTtHt.workorder==2)&(db.WoTtHt.valid==True)).select( > db.HourType.code,db.TaskType.code,left=db.TaskType.on(db.WoTtHt.tasktype== > db.TaskType.id)) > --------------------------------------------------------------------------- > DatabaseError Traceback (most recent call last) > > /home/mariano/Sandbox/web2py/<ipython console> in <module>() > > /home/mariano/Sandbox/web2py/gluon/sql.pyc in select(self, *fields, > **attributes) > 1984 if not attributes.get('cache', None): > 1985 query = self._select(*fields, **attributes) > -> 1986 r = response(query) > 1987 else: > 1988 (cache_model, time_expire) = attributes['cache'] > > /home/mariano/Sandbox/web2py/gluon/sql.pyc in response(query) > 1979 def response(query): > 1980 self._db['_lastsql'] = query > -> 1981 self._db._execute(query) > 1982 return self._db._cursor.fetchall() > 1983 > > /home/mariano/Sandbox/web2py/gluon/sql.pyc in <lambda>(a) > 736 self._cursor = self._connection.cursor() > 737 self._execute = lambda a: \ > --> 738 oracle_fix_execute(a,self._cursor.execute) > 739 self._execute("ALTER SESSION SET NLS_DATE_FORMAT = > 'YYYY-MM-DD';") > 740 self._execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = > 'YYYY-MM-DD HH24:MI:SS';") > > /home/mariano/Sandbox/web2py/gluon/sql.pyc in oracle_fix_execute(command, > execute) > 435 args.append(m.group('clob')[6:-2].replace("''", "'")) > 436 i += 1 > --> 437 return execute(command[:-1], args) > 438 > 439 > > DatabaseError: ORA-00904: "WOTTHT"."TASKTYPE": invalid identifier > > In [26]: db._lastsql > Out[26]: "SELECT HourType.code, TaskType.code FROM WoTtHt, HourType LEFT > OUTER JOIN TaskType ON WoTtHt.tasktype=TaskType.id WHERE > ((WoTtHt.hourtype=HourType.id AND WoTtHt.workorder=2) AND > WoTtHt.valid='T');" > > If I swap the two tables in the from clause, I get the right result (running > this with sqlplus, for example): > > SELECT HourType.code, TaskType.code FROM HourType, WoTtHt LEFT OUTER JOIN > TaskType ON WoTtHt.tasktype=TaskType.id WHERE ((WoTtHt.hourtype=HourType.id > AND WoTtHt.workorder=2) AND WoTtHt.valid='T'); > > However I cannot find a way to make DAL switch the table's order (at least > with all the combinations I tried). > > Mariano > > > On Jun 2, 3:59 am, Mariano Mara <[email protected]> wrote: > > > Good night everyone. > > > > Sorry to bother you guys with this but I'm totally stuck. > > > > I have the following tables defined: > > > > db.define_table("TaskType", db.Field("code", type='string', length=3, > > > required=True, notnull=True, > > > unique=True), > > > db.Field("descrip", type='string', > > > required=True, > > > notnull=True, length=600)) > > > db.define_table("HourType", db.Field("code", type='string', length=3, > > > required=True, notnull=True, > > > unique=True), > > > db.Field("descrip", type='string', > > > required=True, > > > notnull=True, length=600)) > > > db.define_table("WoTtHt", db.Field("workorder", type='integer', > > > notnull=True), > > > db.Field("tasktype", db.TaskType), > > > db.Field("hourtype", db.HourType, > > > notnull=True), > > > db.Field("valid", type='boolean', > > > required=True, > > > notnull=True, default=True)) > > > db.TaskType.insert(code='ADM', descrip='Administration') > > > db.TaskType.insert(code='SAL', descrip='Sales') > > > db.HourType.insert(code='LUN', descrip='Lunch') > > > db.HourType.insert(code='WOR', descrip='Working') > > > db.WoTtHt.insert(workorder=1, tasktype=1, hourtype=1, valid=True) > > > db.WoTtHt.insert(workorder=1, tasktype=2, hourtype=2, valid=True) > > > db.WoTtHt.insert(workorder=2, hourtype=1, valid=True) > > > db.WoTtHt.insert(workorder=2, tasktype=2, hourtype=2, valid=True) > > > > As you can see, HourType is always present and TaskType not always. I > > > want to retrieve the HourType and TaskType codes for a given WorkOrder > > > when the combination is valid: > > > > The following sql gives the right anwer: > > > SELECT HourType.code, TaskType.code FROM HourType, WoTtHt LEFT OUTER > > > JOIN TaskType ON WoTtHt.tasktype=TaskType.id WHERE > > > WoTtHt.hourtype=HourType.id and WoTtHt.workorder=2 and > > > WoTtHt.valid='T'; > > > > however I can't produce a similar result with DAL: > > > for example: > > > op1 = db((db.WoTtHt.hourtype==db.HourType.id)&(db.WoTtHt.workorder==2)& > > > (db.WoTtHt.valid==True)).select( > > > db.HourType.code,db.TaskType.code,left=db.WoTtHt.on > > > (db.WoTtHt.tasktype==db.TaskType.id)) > > > > return rows when tasktype is not null (workorder 1) but it gets > > > nothing when tasktype is null. > > > > The sql I get with db._lastsql isn't quite right (at least compared > > > with the sql I know it works): > > > "SELECT HourType.code, TaskType.code FROM HourType, TaskType LEFT > > > OUTER JOIN WoTtHt ON WoTtHt.tasktype=TaskType.id WHERE > > > ((WoTtHt.hourtype=HourType.id AND WoTtHt.workorder=2) AND > > > WoTtHt.valid='T');" > > > > I tried several combinations to no avail (it seems the sql changes if > > > I change the criteria order in the filter, too). > > > I'm pretty sure it's a silly mistake but if somebody can give a tip on > > > this, I will appreciate it. > > > > Mariano. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" 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 -~----------~----~----~----~------~----~------~--~---
