error in a sql query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

error in a sql query

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-09-08 : 10:14:45

Please can anybody tell me where I am goin wrong on this sql query.
The sql server is giving me an error

Error

Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

Sql Query

Select A.CEO_ID,A.BirthDate,
A.BOROUGH,
A.GENDER,
A.Driver_License_Num,
A.Telephone,
A.First_Name,
A.Last_Name,
A.Address,
A.Val,
A.State,
A.Zip,
A.MARITAL_STATUS,
A.cell_phone_num,
A.Number_of_Children,
A.alt_address,
A.alt_city,
A.alt_state,
A.alt_zip,
A.doc_ready_user_id,
A.SSN,
A.math_score,
A.reading_score,
A.grade_in_school,
A.Salutation,
B.job_coach,
C.job_developer,
D.Retention_Specialist

FROM

(select P.Part_ID,P.CEO_ID,
Convert(varchar(10),P.Birthdate,101) as BirthDate,
CASE WHEN LEFT(P.Zip, 3) = '100'THEN 'Manhattan'
WHEN LEFT(P.Zip, 3) = '103'THEN 'Staten Island'
WHEN LEFT(P.Zip, 3) = '104'THEN 'Bronx'
WHEN LEFT(P.Zip, 3) = '112'THEN 'Brooklyn'
WHEN (LEFT(P.Zip, 3) = '110' or LEFT(P.Zip, 3) = '111' or LEFT(P.Zip, 3) = '113'
or LEFT(P.Zip, 3) = '114' or LEFT(P.Zip, 3) = '116') THEN 'Queens'
WHEN LEFT(P.Zip, 3) is null THEN 'NOT AVAILABLE'
ELSE 'Outside of NYC' END AS BOROUGH ,
CASE WHEN P.Sex = 'M' THEN 'MALE'
WHEN P.Sex = 'F' THEN 'FEMALE'
ELSE 'NONE' END AS GENDER,
pe.Driver_License_Num,
P.Telephone,
P.First_Name,
P.Last_Name,
P.Address,
L.Val,
P.State,
P.Zip,
CASE WHEN P.Marital_Status = 'S' THEN 'SINGLE'
WHEN Marital_Status = 'M' THEN 'MARRIED'
WHEN Marital_Status = 'N' THEN 'NEVER MARRIED'
WHEN Marital_Status = 'D' THEN 'DIVORCED'
ELSE 'NONE' END AS MARITAL_STATUS ,
P.cell_phone_num,
Count(pc1.part_id)AS Number_of_Children,
P.alt_address,
P.alt_city,
P.alt_state,
P.alt_zip,
P.doc_ready_user_id,
P.SSN,
ja.math_score,
ja.reading_score,
ja.grade_in_school,
P.Salutation
from
Part P,List_Vals L ,Part_Employment pe,Part_Children pc1,JTPA_Appl ja
WHERE
P.Part_ID *= pe.Part_ID AND
P.City *= L.Val_ID AND
P.Part_ID *= pc1.part_id AND
P.Part_ID *= ja.part_id AND
P.alt_city *= L.Val_ID AND
L.List_ID = 4
GROUP BY P.Part_ID,P.CEO_ID,P.Birthdate,P.Zip,
pe.Driver_License_Num,
P.Telephone,
P.First_Name,
P.Last_Name,
P.Address,
L.Val,
P.State,
P.Marital_Status,
P.cell_phone_num,
P.Sex,
P.alt_address,
P.alt_city,
P.alt_state,
P.alt_zip,
P.doc_ready_user_id,
P.SSN,
ja.math_score,
ja.reading_score,
ja.grade_in_school,
P.Salutation
)A,
(
SELECT P.Part_ID,U.User_ID as job_coach
from
Part P,Users U
WHERE
P.job_coach_id *= U.User_ID
)B,

(SELECT P.Part_ID,U.User_ID as job_developer
from
Part P, Users U
WHERE
P.User_ID *= U.User_ID
)C,

(
SELECT P.Part_ID,U.User_ID as Retention_Specialist
from
Part P, Users U
WHERE
P.Retention_Specialist *= U.User_ID
)D

where
A.Part_ID *= B.Part_ID AND
A.Part_ID *= C.Part_ID AND
A.Part_ID *= D.Part_ID
Order by A.Part_ID

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-08 : 13:03:10

I've run into as well. I just rewrote the query using the ANSI-92 style i.l.o. the old join style.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources