What's wrong with this SQL 1

[DrBee]

I ran this through SQL Analyzer in MS SQL Server 2000 and I got the following error: "query contains an outer join request that is not permissible". Can someone tell me what is wrong.

SELECT Client.client_id,
Client.client_alerts,
Client.account_manager_name,
Client.contract_start_date,
Client.tpg_service_name,
Client.tpg_phone,
Client.service_level,
Client.primary_service_center_name,
Client.primary_service_center_phone,
Client.account_manager_phone,
Client.num_sessions,
Client.sessions_ind,
Client.short_term_sess_ind,
ml.description,
EAP_Client.session_type,
EAP_Client.sessions_num,
EAP_Client.sessions_num_addl,
EAP_Client.variable_ind,
ml3.description as description_3,
ml2.description as description_2,
ml4.description as description_4,
EAP_Client.consult_authorize_num,
EAP_Client.consult_authorize_ind
FROM Client, EAP_Client, Master_Lookup ml, Master_Lookup_Alpha ml2,
Master_Lookup_Alpha ml3, Master_Lookup_Alpha ml4
WHERE Client.client_id = 999
AND Client.client_id *= EAP_Client.client_id
AND (EAP_Client.session_type *= ml.answer_code
AND ml.category = 'EAP_session')
AND (EAP_Client.consult_authorize_ind *= ml2.alpha_code
AND ml2.category = 'EAP_authorize')
AND (EAP_Client.variable_ind *= ml3.alpha_code
AND ml3.category = 'EAP_variable')
AND (EAP_Client.consult_authorize_ind *= ml4.alpha_code
AND ml4.category = 'EAP_con_auth');

 

[daniel32]

Dr Bee,

I'm not positive on this , but the first thing I would do is check your data types and make sure the fields you are doing the outer joins on are the same type. Also never having worked with SQL Server before, I don't know. But, I noticed you are doing two outer joins on the EAP_Client.consult_authorize_ind column. Is there a chance that SQL Server doesn't like that?

Other than those, I don't notice anything out of the ordinary.

HTH and Good Luck
Dan

 

[rt63]

Your problem is with the outer joins.

You are attempting to put an outer join on a table which itself is getting oute joined by another table - which is not permitted (EAP_Client is the table which is creating problem).

select <something>
from a,b,c
where a.col1*=b.col1
and b.col2*=c.col1

-- not permitted as table b is already on outer join with table a and cannot have a similar join with table c