MS SQL Outer Joins VS Sybase Outer Joins

MS SQL Outer Joins VS Sybase Outer Joins

Post by Mike Fotio » Mon, 18 Dec 2000 12:56:25



I'm working with a development team in converting a SQL Server application
to work with Sybase.  Beyond the typical syntax differences, the major
difficulty we have encountered so far is Sybase's apparent dislike of tables
joined to the first table of an outer join operation:

E.G.

SELECT * FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
WHERE
T1 *= T2
AND T2 = T3   --- OR event T2 *= T3

Additionally, we have a few queries which actually filter out the results of
an inner join based on the values in the right table

E.G.

SELECT * FROM TABLE1 T1, TABLE2 T2
WHERE
T1 *= T2
AND T2.LAST_NAME LIKE 'SMITH%'

Finally, we might have a correlated sub-query that also accesses the value
of a field in a left-joined table:

E.G.

SELECT *, SUPPLIER_NO = (SELECT SUPPLIER_NO FROM TBLSUPPLIER WHERE
SUPPLIER_NO = T2.SUPPLIER_NO AND ACTIVE_FLG = 1)
FROM TABLE1 T1, TABLE2 T2
WHERE
T1 *= T2

All of these queries cause errors in Sybase, seemingly because extra tables
can't be joined to the first table in an outer join clause, or you can't
refer to a field in a left-joined table.  We are currently working around
this problem by creating views to simulate the multi-table left join (i.e.
left joining to a view which is itself a series of inner joins) or by using
a subquery in the WHERE clause to test for a value in the right table.

Are there any other ways around this?  Have any of you guys run into this
problem?  All of these queries will run correctly in SQL Server 7.  We are
using version Sybase 11.0.3, and will be upgrading shortly to 11.9.

Thanks,

Mike

 
 
 

MS SQL Outer Joins VS Sybase Outer Joins

Post by Kenny MacLeo » Tue, 19 Dec 2000 23:44:00


Have you tried using the ANSI JOIN syntax?


Quote:> I'm working with a development team in converting a SQL Server application
> to work with Sybase.  Beyond the typical syntax differences, the major
> difficulty we have encountered so far is Sybase's apparent dislike of
tables
> joined to the first table of an outer join operation:

> E.G.

> SELECT * FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
> WHERE
> T1 *= T2
> AND T2 = T3   --- OR event T2 *= T3

> Additionally, we have a few queries which actually filter out the results
of
> an inner join based on the values in the right table

> E.G.

> SELECT * FROM TABLE1 T1, TABLE2 T2
> WHERE
> T1 *= T2
> AND T2.LAST_NAME LIKE 'SMITH%'

> Finally, we might have a correlated sub-query that also accesses the value
> of a field in a left-joined table:

> E.G.

> SELECT *, SUPPLIER_NO = (SELECT SUPPLIER_NO FROM TBLSUPPLIER WHERE
> SUPPLIER_NO = T2.SUPPLIER_NO AND ACTIVE_FLG = 1)
> FROM TABLE1 T1, TABLE2 T2
> WHERE
> T1 *= T2

> All of these queries cause errors in Sybase, seemingly because extra
tables
> can't be joined to the first table in an outer join clause, or you can't
> refer to a field in a left-joined table.  We are currently working around
> this problem by creating views to simulate the multi-table left join (i.e.
> left joining to a view which is itself a series of inner joins) or by
using
> a subquery in the WHERE clause to test for a value in the right table.

> Are there any other ways around this?  Have any of you guys run into this
> problem?  All of these queries will run correctly in SQL Server 7.  We are
> using version Sybase 11.0.3, and will be upgrading shortly to 11.9.

> Thanks,

> Mike


 
 
 

MS SQL Outer Joins VS Sybase Outer Joins

Post by Mike Fotio » Wed, 20 Dec 2000 05:59:47


What is the ANSI JOIN syntax - INNER/LEFT/RIGHT?  I thinkSQL Server
supported up to SQL-92, while it appears Sybase 11 supports only SQL-89,
which I thought was the *=/=/=* syntax.  Am I wrong?

Thanks,

Mike


> Have you tried using the ANSI JOIN syntax?



> > I'm working with a development team in converting a SQL Server
application
> > to work with Sybase.  Beyond the typical syntax differences, the major
> > difficulty we have encountered so far is Sybase's apparent dislike of
> tables
> > joined to the first table of an outer join operation:

> > E.G.

> > SELECT * FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
> > WHERE
> > T1 *= T2
> > AND T2 = T3   --- OR event T2 *= T3

> > Additionally, we have a few queries which actually filter out the
results
> of
> > an inner join based on the values in the right table

> > E.G.

> > SELECT * FROM TABLE1 T1, TABLE2 T2
> > WHERE
> > T1 *= T2
> > AND T2.LAST_NAME LIKE 'SMITH%'

> > Finally, we might have a correlated sub-query that also accesses the
value
> > of a field in a left-joined table:

> > E.G.

> > SELECT *, SUPPLIER_NO = (SELECT SUPPLIER_NO FROM TBLSUPPLIER WHERE
> > SUPPLIER_NO = T2.SUPPLIER_NO AND ACTIVE_FLG = 1)
> > FROM TABLE1 T1, TABLE2 T2
> > WHERE
> > T1 *= T2

> > All of these queries cause errors in Sybase, seemingly because extra
> tables
> > can't be joined to the first table in an outer join clause, or you can't
> > refer to a field in a left-joined table.  We are currently working
around
> > this problem by creating views to simulate the multi-table left join
(i.e.
> > left joining to a view which is itself a series of inner joins) or by
> using
> > a subquery in the WHERE clause to test for a value in the right table.

> > Are there any other ways around this?  Have any of you guys run into
this
> > problem?  All of these queries will run correctly in SQL Server 7.  We
are
> > using version Sybase 11.0.3, and will be upgrading shortly to 11.9.

> > Thanks,

> > Mike

 
 
 

MS SQL Outer Joins VS Sybase Outer Joins

Post by Kenny MacLeo » Wed, 20 Dec 2000 17:31:28


ASE 12 supports ANSI joins as you describe (except for FULL OUTER).  Version
11 doesn't, though.


> What is the ANSI JOIN syntax - INNER/LEFT/RIGHT?  I thinkSQL Server
> supported up to SQL-92, while it appears Sybase 11 supports only SQL-89,
> which I thought was the *=/=/=* syntax.  Am I wrong?

> Thanks,

> Mike



> > Have you tried using the ANSI JOIN syntax?



> > > I'm working with a development team in converting a SQL Server
> application
> > > to work with Sybase.  Beyond the typical syntax differences, the major
> > > difficulty we have encountered so far is Sybase's apparent dislike of
> > tables
> > > joined to the first table of an outer join operation:

> > > E.G.

> > > SELECT * FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
> > > WHERE
> > > T1 *= T2
> > > AND T2 = T3   --- OR event T2 *= T3

> > > Additionally, we have a few queries which actually filter out the
> results
> > of
> > > an inner join based on the values in the right table

> > > E.G.

> > > SELECT * FROM TABLE1 T1, TABLE2 T2
> > > WHERE
> > > T1 *= T2
> > > AND T2.LAST_NAME LIKE 'SMITH%'

> > > Finally, we might have a correlated sub-query that also accesses the
> value
> > > of a field in a left-joined table:

> > > E.G.

> > > SELECT *, SUPPLIER_NO = (SELECT SUPPLIER_NO FROM TBLSUPPLIER WHERE
> > > SUPPLIER_NO = T2.SUPPLIER_NO AND ACTIVE_FLG = 1)
> > > FROM TABLE1 T1, TABLE2 T2
> > > WHERE
> > > T1 *= T2

> > > All of these queries cause errors in Sybase, seemingly because extra
> > tables
> > > can't be joined to the first table in an outer join clause, or you
can't
> > > refer to a field in a left-joined table.  We are currently working
> around
> > > this problem by creating views to simulate the multi-table left join
> (i.e.
> > > left joining to a view which is itself a series of inner joins) or by
> > using
> > > a subquery in the WHERE clause to test for a value in the right table.

> > > Are there any other ways around this?  Have any of you guys run into
> this
> > > problem?  All of these queries will run correctly in SQL Server 7.  We
> are
> > > using version Sybase 11.0.3, and will be upgrading shortly to 11.9.

> > > Thanks,

> > > Mike

 
 
 

MS SQL Outer Joins VS Sybase Outer Joins

Post by Mike Fotio » Thu, 21 Dec 2000 22:22:03


OK, I tried it out with 12, and SQL Server code is accepted and processed
correctly.  Of course, lucky us, the client we work for (government) will
probably not move to 12 for another year :)

Thanks,

Mike


> ASE 12 supports ANSI joins as you describe (except for FULL OUTER).
Version
> 11 doesn't, though.



> > What is the ANSI JOIN syntax - INNER/LEFT/RIGHT?  I thinkSQL Server
> > supported up to SQL-92, while it appears Sybase 11 supports only SQL-89,
> > which I thought was the *=/=/=* syntax.  Am I wrong?

> > Thanks,

> > Mike



> > > Have you tried using the ANSI JOIN syntax?



> > > > I'm working with a development team in converting a SQL Server
> > application
> > > > to work with Sybase.  Beyond the typical syntax differences, the
major
> > > > difficulty we have encountered so far is Sybase's apparent dislike
of
> > > tables
> > > > joined to the first table of an outer join operation:

> > > > E.G.

> > > > SELECT * FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
> > > > WHERE
> > > > T1 *= T2
> > > > AND T2 = T3   --- OR event T2 *= T3

> > > > Additionally, we have a few queries which actually filter out the
> > results
> > > of
> > > > an inner join based on the values in the right table

> > > > E.G.

> > > > SELECT * FROM TABLE1 T1, TABLE2 T2
> > > > WHERE
> > > > T1 *= T2
> > > > AND T2.LAST_NAME LIKE 'SMITH%'

> > > > Finally, we might have a correlated sub-query that also accesses the
> > value
> > > > of a field in a left-joined table:

> > > > E.G.

> > > > SELECT *, SUPPLIER_NO = (SELECT SUPPLIER_NO FROM TBLSUPPLIER WHERE
> > > > SUPPLIER_NO = T2.SUPPLIER_NO AND ACTIVE_FLG = 1)
> > > > FROM TABLE1 T1, TABLE2 T2
> > > > WHERE
> > > > T1 *= T2

> > > > All of these queries cause errors in Sybase, seemingly because extra
> > > tables
> > > > can't be joined to the first table in an outer join clause, or you
> can't
> > > > refer to a field in a left-joined table.  We are currently working
> > around
> > > > this problem by creating views to simulate the multi-table left join
> > (i.e.
> > > > left joining to a view which is itself a series of inner joins) or
by
> > > using
> > > > a subquery in the WHERE clause to test for a value in the right
table.

> > > > Are there any other ways around this?  Have any of you guys run into
> > this
> > > > problem?  All of these queries will run correctly in SQL Server 7.
We
> > are
> > > > using version Sybase 11.0.3, and will be upgrading shortly to 11.9.

> > > > Thanks,

> > > > Mike