#1228: Table alias not used in where clause by SQLBuilder method (Parent-child
situation)
-----------------------+----------------------------------------------------
Reporter: skoczian | Owner: somebody
Type: defect | Status: new
Priority: major | Milestone: 0.9.2
Component: biz | Version: 0.9.1
Resolution: | Keywords: alias where clause
-----------------------+----------------------------------------------------
Old description:
> Situation: Two bizobjs, parent and child. Select queries for both created
> by SQLBuilder methods. Child bizobj uses two joined tables and an alias
> for one of them:
>
> class BizEmpProj(dabo.biz.dBizobj):
> def initProperties(self):
> self.DataSource = 'employee_project'
> self.KeyField = ('proj_id', 'emp_no')
> self.LinkField = 'proj_id'
> self.AutoPopulatePK = False
> self.addFrom('employee_project', 'epr')
> self.addJoin('employee', 'epr.emp_no = employee.emp_no')
> self.addField('epr.emp_no')
> self.addField('epr.proj_id')
> self.addField('employee.first_name')
> self.addField('employee.last_name')
> self.addOrderBy('employee.last_name')
> self.DataStructure = [('proj_id', 'C', True, 'epr',
> 'proj_id'),
> ('emp_no', 'I', True, 'epr', 'emp_no'),
> ('first_name', 'C', False, 'employee',
> 'first_name'),
> ('last_name', 'C', False, 'employee',
> 'last_name')]
>
> Error message at the start of the application:
>
> (-206, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code =
> -206\n Column unknown\n EMPLOYEE_PROJECT.PROJ_ID\n At line 9, column
> 11')
> SQL: SELECT first 1000
> epr."EMP_NO",
> epr."PROJ_ID",
> employee."FIRST_NAME",
> employee."LAST_NAME"
> from employee_project as epr
> inner join employee on epr."EMP_NO" = employee."EMP_NO"
> where employee_project."PROJ_ID" = 'VBASE'
> order by employee."LAST_NAME"
>
> In fact this query is wrong: the where clause should use the table alias.
New description:
Situation: Two bizobjs, parent and child. Select queries for both created
by SQLBuilder methods. Child bizobj uses two joined tables and an alias
for one of them:
{{{
#!python
class BizEmpProj(dabo.biz.dBizobj):
def initProperties(self):
self.DataSource = 'employee_project'
self.KeyField = ('proj_id', 'emp_no')
self.LinkField = 'proj_id'
self.AutoPopulatePK = False
self.addFrom('employee_project', 'epr')
self.addJoin('employee', 'epr.emp_no = employee.emp_no')
self.addField('epr.emp_no')
self.addField('epr.proj_id')
self.addField('employee.first_name')
self.addField('employee.last_name')
self.addOrderBy('employee.last_name')
self.DataStructure = [('proj_id', 'C', True, 'epr',
'proj_id'),
('emp_no', 'I', True, 'epr', 'emp_no'),
('first_name', 'C', False, 'employee',
'first_name'),
('last_name', 'C', False, 'employee',
'last_name')]
}}}
Error message at the start of the application:
{{{
#!python
(-206, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -206\n
Column unknown\n EMPLOYEE_PROJECT.PROJ_ID\n At line 9, column 11')
SQL: SELECT first 1000
epr."EMP_NO",
epr."PROJ_ID",
employee."FIRST_NAME",
employee."LAST_NAME"
from employee_project as epr
inner join employee on epr."EMP_NO" = employee."EMP_NO"
where employee_project."PROJ_ID" = 'VBASE'
order by employee."LAST_NAME"
}}}
In fact this query is wrong: the where clause should use the table alias.
Comment (by paul):
I just fixed up the formatting of the ticket. I assume this is still an
issue?
--
Ticket URL: <http://trac.dabodev.com/ticket/1228#comment:1>
Dabo Trac <http://trac.dabodev.com>
Trac Page for Dabo
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message:
http://leafe.com/archives/byMID/[email protected]
