Solved

Query contains an outer-join request that is not permitted - Sybase to SQL Server conversion

Posted on 2004-10-17
3
1,821 Views
Last Modified: 2012-08-14
Hi guys!

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:

SELECT  
 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
 FROM
client_case c, centre, case_centre_share cs, service s, service_client sc, service_item si  
 WHERE
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

Thanks!
P.S. I'll be online all day and will test any suggested SQLs against my database right away!

0
Comment
Question by:vlad_l
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
doryllis earned 500 total points
ID: 12335268
My understanding on this is that order matters somewhat.  Try using this syntax for the SQL Server.

SELECT  
 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
 FROM
 client_case c
  left outer join centre
    on c.centre_id = centre.id
  left outer join case_centre_share cs
    on  c.id = cs.case_id    
  left outer join service_client sc
    on   c.id = sc.case_id
  left outer join service s
    on   sc.service_id = s.id
  left outer join service_item si
    on s.service_item = si.id  
 WHERE
      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
0
 

Author Comment

by:vlad_l
ID: 12335735
thanks a lot! it works just fine! :-)
0
 
LVL 2

Expert Comment

by:doryllis
ID: 12337770
Glad I could help.  
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 113
Cannot install / add SQL database when installing MyCalls software 22 105
Update cached table in H2 database 6 58
use lov values 2 50
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question