Query contains an outer-join request that is not permitted - Sybase to SQL Server conversion
Posted on 2004-10-17
Doing a project converting a Sybase ASE 12.0 database to SQL Server 2000.
Pretty much everything converts straight away with little syntax changes, but hit a bit of a stumbling block on this one:
c.id, c.centre_id, c.client_code, c.case_open, c.open_by, c.case_worker, c.close_date, c.closed_by, c.closed_reason,
c.presenting_issue, c.referral_src, c.service_needed, c.identity_proof, c.referral_note, centre.name as centre_name,
c.achieve_level, c.graduated, c.agree_case_mgnt, c.referral_contact, c.referral_address, c.referral_phone,
cs.centre_id as user_logon_centre_id, c.consent, c.income_source, c.living_situation, ref_no,
isNull(s.start_date, null) as last_assistance_date
client_case c, centre, case_centre_share cs, service s, service_client sc, service_item si
c.centre_id *= centre.id
AND c.id *= cs.case_id
AND c.id *= sc.case_id
AND sc.service_id *= s.id
AND s.service_item *= si.id
AND sc.id = (select max(id) from service_client sc2 where sc2.case_id = sc.case_id)
AND s.method = 'AE'
ORDER BY c.id desc
The SQL runs fine in Sybase but when I try to run it against a SQL Server database I get
"Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted."
Now, I am aware that I SQL Server is using different sort of sytanx to create joins (LEFT OUTER, RIGHT OUTER etc) but the manual says that it would support a (*=) type of synax well. Could anyone tell me where the "non permitted" outer join is in my SQL?.. or even better.. what the proper syntax for SQL Server should be
P.S. I'll be online all day and will test any suggested SQLs against my database right away!