Joins Problem 
Author Message
 Joins Problem

I have the following select which I need to perform inside my Powerbuilder application through a datawindow.
My problem is that I need in the where clause 2 table joins one of which is a regular and the other a inner join.
I know that in Sybase this is not allowed but do not know how to re-write the SQL so that it will work. I was thinking
of having the table l_d2gvl again in the where clause but with a different alias but I could not get it to work.

SELECT c.name1, p.name, p.vorname, p.salutationid, vft.local_name, vd.local_name,
p.telefon, p.telefax, p.linktype, l_c2gvl.gvlid
FROM vis_customer c, personen p, l_cust2gvl l_c2gvl, vis_function_type vft, vis_department vd, l_gvl2region gr
WHERE c.custid = p.linkid AND p.linktype = "C" AND c.custid *= l_c2gvl.custid AND p.funktionid *= vft.function_code
AND p.abteilung *= vd.dept_code AND gr.gvlid = l_c2gvl.gvlid AND gr.regionid = :regionid
                                                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UNION
SELECT d.name1, p.name, p.vorname, p.salutationid, vft.local_name, vd.local_name,
p.telefon, p.telefax, p.linktype, l_d2gvl.gvlid
FROM dealer d, personen p , l_deal2gvl l_d2gvl, vis_function_type vft, vis_department vd, l_gvl2region gr
WHERE d.dealid = p.linkid AND p.linktype = "D" AND d.dealid *= l_d2gvl.dealid AND p.funktionid *= vft.function_code
AND p.abteilung *= vd.dept_code AND gr.gvlid = l_d2gvl.gvlid AND gr.regionid = :regionid
                                                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The actual error message I get is :

l_cust2gvl is an inner member of an outer-join clasue. This is not allowed if the table also particpates in a regular
join clause.

BTW for none Powerbuilder people the :regionid is just a variable.

Thanks in advance.

Narinder



Sat, 01 Feb 1997 07:11:36 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. SQL, VB, Access, Queries, Join Problem, and Newbie Problem

2. Outer Join Problem

3. Outer join problem (MSSQL 6.5)

4. Join problems with user defined datatypes

5. SQL Server inner join problem

6. Multiple Outer Join Problems with SQL Server and PB (cross-posted)

7. Join Problem

8. Join Problem in SQL 7

9. outer join problem

10. Outer join problems with VB, Access, and SQL Server

11. ODBC SQL join problem

12. Left Outer Join Problem


 
Powered by phpBB® Forum Software