Roger McIlmoyle
|
| Posted: 04/02/2002, 9:41 AM |
|
I have a case where I've had to code the sql statement for a form. I've also
included a column as an input variable. The problem is I'm joining two
tables with the same column name, hence the resulting where statement does
not fully qualify the column and sql reports an ambiguous column error.
eg.
select t.user_id from timesheet t, userinfo u where t.user_id = u.user_id
user_id is a column for which input parameters limit
resulting sql statement becomes
select t.user_id from timesheet t, userinfo u where t.user_id = u.user_id
and user_id = 1
Error **** user_id is not qualified.
What's the best way around this ? besides using the form open event and
handling the input parameter there. The reason this does not work is I have
to do a ton of code to handle passing this parameter to other forms then.
|
|
|
 |
RonB
|
| Posted: 04/02/2002, 12:47 PM |
|
On Tue, 02 Apr 2002 19:40:32 +0200, Roger McIlmoyle wrote:
> resulting sql statement becomes
>
> select t.user_id from timesheet t, userinfo u where t.user_id =
> u.user_id and user_id = 1
>
> Error **** user_id is not qualified.
>
I think the best way is to construct the sql statment yourself in the sql
section. There you'd use the fullnames like:
select timesheet.user_id from timesheet, userinfo
where timesheet.user_id=userinfo.user_id and....
here's where the problem starts. Because you didn't select any column
from the userinfo table there is no field to pass the input parameter to.
If you just want to pass it to timesheet.user_id there's no problem. You
can use codecharge to do that for you. In the table section
timesheet.user_id is the only field you can choose since your select
section of the statement contains only this column. If you want to pass
the input parameter to userinfo.user_id you'll have to put it into the
select statement and , if you do not want it to show on the form, make it
hidden instead of label.
you route the input parameter to a field via the input section by
choosing a field and naming the variable it will respond to. So set it to
field timesheet.user_id, or userinfo.user_id if you choose to include it
in the select section of your statement.
joining to a table you do not use on your form seems a bit overdone.
RonB
|
|
|
|
Roger McIlmoyle
|
| Posted: 04/02/2002, 3:06 PM |
|
Sorry, I was trying to simplify the actual sql statement for example
purposes,
The actual statement was:
select t.user_id,
t.payweek_id,
u.user_name,
p.weekstart,
y.description,
t.mgradjust,
t.amount,
t.mgrcomment,
t.mon_hrs+t.tue_hrs+t.wed_hrs+t.thu_hrs+t.fri_hrs+hradjust totaltime,
t.sat_hrs,
t.sun_hrs,
t.mon_hrs,
t.tue_hrs,
t.wed_hrs,
t.thu_hrs,
t.fri_hrs,
t.approved,
t.timesheet_id,
t.timetype_id,
d.abreviation,
t.hradjust,
t.hrcomment,
n.timesheet_id notes
from
timesheet t,
userinfo u,
payweek p,
departments d,
timetype y,
timesheetnotes n
where t.user_id = u.user_sys_id
and t.timetype_id = y.timetype_sys_id
and t.payweek_id = p.payweek_sys_id
and u.dept_id = d.dept_sys_id
and t.timesheet_id *= n.timesheet_id
Keeping in mind table userinfo also has a column named user_id it conflicts
with user_id in timesheet since the generated code does not fully qualify
the column user_id used in the form grid.
I resolved it by:
select t.user_id,
t.payweek_id,
u.user_name,
p.weekstart,
y.description,
t.mgradjust,
t.amount,
t.mgrcomment,
t.mon_hrs+t.tue_hrs+t.wed_hrs+t.thu_hrs+t.fri_hrs+hradjust totaltime,
t.sat_hrs,
t.sun_hrs,
t.mon_hrs,
t.tue_hrs,
t.wed_hrs,
t.thu_hrs,
t.fri_hrs,
t.approved,
t.timesheet_id,
t.timetype_id,
d.abreviation,
t.hradjust,
t.hrcomment,
n.timesheet_id notes
from
timesheet t,
( select user_name,user_sys_id,dept_id from userinfo ) u,
payweek p,
departments d,
timetype y,
timesheetnotes n
where t.user_id = u.user_sys_id
and t.timetype_id = y.timetype_sys_id
and t.payweek_id = p.payweek_sys_id
and u.dept_id = d.dept_sys_id
and t.timesheet_id *= n.timesheet_id
The sub select virtulizes the table an elimnates the ambiguous reference to
user_id. However, I generally don't like to use sub-selects since it looses
the key and turns the query into a table scan.
I was hoping to fully qualify the column name in the generated where clause.
Even though I've qualified t.user_id in the select statement it does not
appear that way in the input field drop down.
"RonB" <r.borkent@chello123.nl> wrote in message
news:a8d5ct$6pj$1@news.codecharge.com...
> On Tue, 02 Apr 2002 19:40:32 +0200, Roger McIlmoyle wrote:
>
>
> > resulting sql statement becomes
> >
> > select t.user_id from timesheet t, userinfo u where t.user_id =
> > u.user_id and user_id = 1
> >
> > Error **** user_id is not qualified.
> >
>
> I think the best way is to construct the sql statment yourself in the sql
> section. There you'd use the fullnames like:
> select timesheet.user_id from timesheet, userinfo
> where timesheet.user_id=userinfo.user_id and....
>
> here's where the problem starts. Because you didn't select any column
> from the userinfo table there is no field to pass the input parameter to.
> If you just want to pass it to timesheet.user_id there's no problem. You
> can use codecharge to do that for you. In the table section
> timesheet.user_id is the only field you can choose since your select
> section of the statement contains only this column. If you want to pass
> the input parameter to userinfo.user_id you'll have to put it into the
> select statement and , if you do not want it to show on the form, make it
> hidden instead of label.
> you route the input parameter to a field via the input section by
> choosing a field and naming the variable it will respond to. So set it to
> field timesheet.user_id, or userinfo.user_id if you choose to include it
> in the select section of your statement.
>
> joining to a table you do not use on your form seems a bit overdone.
>
> RonB
|
|
|
|
RonB
|
| Posted: 04/03/2002, 1:19 AM |
|
On Wed, 03 Apr 2002 01:06:21 +0200, Roger McIlmoyle wrote:
> Sorry, I was trying to simplify the actual sql statement for example
> purposes,
I understand. I've had this problem in the beginning, when first
designing the company intranet. I decided it was worth my time to
redesign the database to avoid these problems, but i was still at the
beginning of the design stage. I renamed all table columns by starting
with the first letters of the tablenames:
userinfo would then have column names like:
usinfo_user_id
usinfo_user_name
etc.
If you do this with all tables the chances of running in to columns with
the same names are almost zero. I'm now halfway through the design proces
and have about 94 tables with no maching column names.
I use MySql for the intranet pages and work with Oracle for management
info pages. Oracle supports subselects but MySql doesn't so you see for
me the redesigning of the mysql database payed of.
RonB
|
|
|
|
|
