Adaptive Server versions 12.5 and later do not process correlated subqueries containing Transact-SQL outer joins in the same way that earlier versions of Adaptive Server did. For more information, see “Joins: Retrieving Data from Several Tables.” The following is an example of a query using a correlated variable as the outer member of a Transact-SQL outer join:
select t2.b1, (select t2.b2 from t1 where t2.b1 *= t1.a1) from t2
Earlier versions of Adaptive Server used trace flag 298 to display error messages for these queries. Depending on whether trace flag 298 was turned on or off and whether the query used the correlated variable as an inner or outer member of an outer join, Adaptive Server displayed the behavior described in Table 5-1:
Type of query |
Trace flag 298 turned off |
Trace flag 298 turned on |
|---|---|---|
Correlated as an inner member of an outer join |
Disallowed: produces error message 11013 |
No error |
Correlated as an outer member of an outer join |
No error |
Disallowed: produces error message 301 |
Adaptive Server reverses the behavior of trace flag 298. Because Adaptive Server versions 12.5 and later translate Transact-SQL outer joins into ANSI outer joins during the preprocessor stage, there is the potential for different results when allowing such queries to run. Allowing correlated subqueries that contain Transact-SQL outer joins to run with the 298 trace flag turned on is consistent with the Sybase historical trace flag usage. In versions 12.5 and later, the behavior of trace flag 298 is:
Type of query |
Trace flag 298 turned off |
Trace flag 298 turned on |
|---|---|---|
Correlated as an inner member of an outer join |
Disallowed: produces error message 11013 |
Disallowed: produces error message 11013 |
Correlated as an outer member of an outer join |
Disallowed: produces error message 11055 |
No error |
Adaptive Server has changed error message 301 to error
message 11055, although the text of the message remains the same.
