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