I do not see why the order of the two tables in FROM ... matters at
all.
With the current web2py you cannot specify the order. Sorry.
But I will keep thinking about it.
Massimo
On Jun 2, 11:53 am, Mariano Mara <mariano.m...@gmail.com> wrote:
> 2009/6/2 mdipierro <mdipie...@cs.depaul.edu>
>
>
>
> > 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))
>
> thanks for the followup. Unfortunately, it doesn't work either (below you
> can see the error trace). Could it be an Oracle/DAL related problem?
>
> In [27]: op1 = 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))
> ---------------------------------------------------------------------------
> 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 [28]: db._lastsql
> Out[28]: "SELECT HourType.code, TaskType.code FROM WoTtHt, HourType LEFT
> OUTER JOIN TaskType ON WoTtHt.tasktype=TaskType.id WHERE
> ((HourType.id=WoTtHt.hourtype AND WoTtHt.workorder=2) AND
> WoTtHt.valid='T');"
>
>
>
> > On Jun 2, 9:42 am, Mariano Mara <mariano.m...@gmail.com> wrote:
> > > 2009/6/2 mdipierro <mdipie...@cs.depaul.edu>
>
> > > > 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 <mariano.m...@gmail.com> 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 web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
