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
-~----------~----~----~----~------~----~------~--~---
