Joined tables cannot be specified in a query conta

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joined tables cannot be specified in a query conta

Author

Topic

binoaf14
Starting Member

2 Posts

Posted - 2008-12-23 : 04:39:52

Hi Friends,

i am getting the following error message. "Joined tables cannot be specified in a query containing outer join operators". This query is running fine in my Dev environment but it throws the same error in QA env. what could be the problem...

Here is the Query:

Select tab1.T_TASK_TYPE_NAME, tab1.T_TASK_PRIORITY_NAME, tab1.T_NUMBER_OF_TASKS ,isnull((100*tab2.T_NUMBER_OF_TASKS/tab1.T_NUMBER_OF_TASKS), 0) as T_ONTIME_PERCENTAGE from (select T.PROJECT_MODULE_ID as T_TASK_TYPE_ID, T.TASK_TYPE as T_TASK_TYPE_NAME, T.TASK_PRIORITY as T_TASK_PRIORITY_NAME, count(*) as T_NUMBER_OF_TASKS from TASK_VW_7 T where T.PROJECT_ID=58056 and T.COMPANY_ID=43415 and ((YEAR(T.START_DATE)=2008 AND MONTH(T.START_DATE)=12) OR (YEAR(T.DATE_EXPECTED_END_TASK)=2008 AND MONTH(T.DATE_EXPECTED_END_TASK)=12)) group by T.PROJECT_MODULE_ID, T.TASK_TYPE, T.PRIORITY_TYPE_ID, T.TASK_PRIORITY )tab1,(select T.PROJECT_MODULE_ID as T_TASK_TYPE_ID,T.TASK_TYPE as T_TASK_TYPE_NAME, T.TASK_PRIORITY as T_TASK_PRIORITY_NAME, count(*)as T_NUMBER_OF_TASKS from TASK_VW_7 T where T.PROJECT_ID=58056 and T.COMPANY_ID=43415 and ((YEAR(T.START_DATE)=2008 AND MONTH(T.START_DATE)=12) OR (YEAR(T.DATE_EXPECTED_END_TASK)=2008 AND MONTH(T.DATE_EXPECTED_END_TASK)=12)) and convert(datetime,T.date_expected_end_task,101) >= convert(datetime,isnull(T.actual_end_date,T.date_expected_end_task),101) and T.TASK_STATUS = 'completed' group by T.PROJECT_MODULE_ID, T.TASK_TYPE, T.PRIORITY_TYPE_ID, T.TASK_PRIORITY ) tab2 where tab1.T_TASK_TYPE_NAME *= tab2.T_TASK_TYPE_NAME and tab1.T_TASK_PRIORITY_NAME *= tab2.T_TASK_PRIORITY_NAME

Regards,
Bino B

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 09:59:33

No chance for me to test it but it should work.
For future posts: please post statements in a readable format.

Select 
tab1.T_TASK_TYPE_NAME, 
tab1.T_TASK_PRIORITY_NAME, 
tab1.T_NUMBER_OF_TASKS ,
isnull((100*tab2.T_NUMBER_OF_TASKS/tab1.T_NUMBER_OF_TASKS), 0) as T_ONTIME_PERCENTAGE 

from (select 
		T.PROJECT_MODULE_ID as T_TASK_TYPE_ID, 
		T.TASK_TYPE as T_TASK_TYPE_NAME, 
		T.TASK_PRIORITY as T_TASK_PRIORITY_NAME, 
		count(*) as T_NUMBER_OF_TASKS 
	   from TASK_VW_7 T 
	   where T.PROJECT_ID=58056 and 
	         T.COMPANY_ID=43415 and 
	         ((YEAR(T.START_DATE)=2008 AND MONTH(T.START_DATE)=12) OR 
	          (YEAR(T.DATE_EXPECTED_END_TASK)=2008 AND MONTH(T.DATE_EXPECTED_END_TASK)=12)) 
	    group by T.PROJECT_MODULE_ID, T.TASK_TYPE, T.PRIORITY_TYPE_ID, T.TASK_PRIORITY )
tab1
LEFT JOIN	    
	 (select 
		T.PROJECT_MODULE_ID as T_TASK_TYPE_ID,
		T.TASK_TYPE as T_TASK_TYPE_NAME, 
		T.TASK_PRIORITY as T_TASK_PRIORITY_NAME, 
		count(*)as T_NUMBER_OF_TASKS 
	   from TASK_VW_7 T 
	   where T.PROJECT_ID=58056 and 
	         T.COMPANY_ID=43415 and 
	         ((YEAR(T.START_DATE)=2008 AND MONTH(T.START_DATE)=12) OR 
	          (YEAR(T.DATE_EXPECTED_END_TASK)=2008 AND MONTH(T.DATE_EXPECTED_END_TASK)=12)) and 
	          convert(datetime,T.date_expected_end_task,101) >= 
	          convert(datetime,isnull(T.actual_end_date,T.date_expected_end_task),101) and 
	         T.TASK_STATUS = 'completed' 
	    group by T.PROJECT_MODULE_ID, T.TASK_TYPE, T.PRIORITY_TYPE_ID, T.TASK_PRIORITY ) 
tab2 
ON tab1.T_TASK_TYPE_NAME = tab2.T_TASK_TYPE_NAME and 
   tab1.T_TASK_PRIORITY_NAME = tab2.T_TASK_PRIORITY_NAME

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-12-23 : 15:09:20

Here's one of several FREE SQL formatters on line. Copy your code in, format it for whatever you're building (MS Access, MSSQL, MYSQL, etc.) and output to various formats (SQL types, C#, VB.Net, etc.). It makes it nice and consistent with accurate, readable formatting making everyone's life a little easier.

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

--webfred, what do you use?

Terry

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-23 : 15:11:49

Hi Terry,

what a shame! I use my fingers and ssms