Large SQL Statement

[afortney]

The following SQL Statement works ib MS SQL server but will not work in Access. The error is Erroor in syntax of query expresttion. The error occurs in the middle of the statement where the the text is the following "page.order_id select (select max(answer) from answer"

select (select max(answer.answer) from answer where answer.member_id in (
select member_id from team_members where project_id in ( select project_id
from project where Business_stream='Upstream' and stage='Appraise' and
project_id in (select project_id from projectextra where subteam<>1 ) ) ) and
answer.page_id=page.page_id) as thinl, (select max(avgscore) from task_project
where task_project.project_id not in (select project_id from projectextra
where subteam=1 ) and task_project.project_id in (select project_id from
project where stage='Appraise' and Business_stream = 'Upstream') and
task_project.page_id=page.page_id) as bmax, (select max(answer) from answer
where answer.page_id=page.page_id) as datamax, (select avg(avgscore) from
task_project where project_id=1 and task_project.page_id=page.page_id) as
projavg, (select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and
task_project.page_id=page.page_id) as companyavg, (select avg(avgscore) from
task_project where project_id not in (select project_id from projectextra
where subteam=1) and project_id in (select project_id from project where
Business_stream = 'Upstream') and task_project.page_id=page.page_id) as
businessavg, page.* from page,riverorder where page.category_name='Business
Boundaries' and stage_name='Appraise' and
riverorder.category_name=page.category_name order by
riverorder.riverorder,page.order_id select (select max(answer.answer) from
answer where answer.member_id in ( select member_id from team_members where
project_id in ( select project_id from project where
Business_stream='Upstream' and stage='Appraise' and project_id in (select
project_id from projectextra where subteam<>1 ) ) ) and
answer.page_id=page.page_id) as thinl, (select max(avgscore) from task_project
where task_project.project_id not in (select project_id from projectextra
where subteam=1 ) and task_project.project_id in (select project_id from
project where stage='Appraise' and Business_stream = 'Upstream') and
task_project.page_id=page.page_id) as bmax, (select max(answer) from answer
where answer.page_id=page.page_id) as datamax, (select avg(avgscore) from
task_project where project_id=1 and task_project.page_id=page.page_id) as
projavg, (select avg(avgscore) from task_project where project_id not in
(select project_id from projectextra where subteam=1) and
task_project.page_id=page.page_id) as companyavg, (select avg(avgscore) from
task_project where project_id not in (select project_id from projectextra
where subteam=1) and project_id in (select project_id from project where
Business_stream = 'Upstream') and task_project.page_id=page.page_id) as
businessavg, page.* from page,riverorder where page.category_name='Business
Boundaries' and stage_name='Appraise' and
riverorder.category_name=page.category_name order by
riverorder.riverorder,page.order_id

 

[Alexandre]

What instruction did you use to execue the query? I remember having read something about string lenght limitation to 255 char existing or not according to what command you use to execute the query... Was it with Docmd.runSQL? You should have a look to the related to the command you used.

Hope this helps.

Alex


[This message has been edited by Alexandre (edited 08-29-2001).]

 

[afortney]

I just ran it using the "Run" prompt inside of MS Access. The query bombs out after the
" "page.order_id select (select max(answer) from
answer"
part, is there anyway I can modify the syntax so it will work?

 

[Alexandre]

Maybe think about splitting the query into several ones. Either base one upon the results of the other or merge the results using a Union query if you have similar partial queries structure and a common field.

Hopes it makes sense.

Alex


Yeah, this should be the solution. I can see that you have several select queries embedded here. creating various simple select queries and using them in a new one should save char in the SQL. EX:

Query: Q1
select max(avgscore) from task_project
where task_project.project_id not in (select project_id from projectextra
where subteam=1

and this SELECT embedded statement can be replace by a reference to Q1. Use the query designer to inculde previously defined partial queries in a new one.

Alex


[This message has been edited by Alexandre (edited 08-29-2001).]