by 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