Left Outer Join

Submitted By: Hadley Garbart

MICA Issue ID: 1244

Oracle PeopleSoft Issue ID: 201039880

HEUG Issue ID:

Status: Resolved

When testing the way that we have become accustomed to in creating a left outer join (this is a portion from the current query)

[in the where clause]
AND A.EMPLID*=G.EMPLID AND 'A' = 'A' …

the following error results

SQL error. Stmt #: 5682 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]Executing SQL directly; no cursor. [Microsoft][SQL Native Client][SQL Server]The query uses non-ANSI outer join operators ('*=' or '=*'). To run this query without modification, please set the compatibility level
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=8601, Message=[Microsoft][SQL Native Client][SQL Server]Executing SQL directly; no cursor. [Microsoft][SQL Native Client][SQL Server]The query uses non-ANSI outer join operators ('*=' or '=*'). To run this query without modification, please set the compatibility level (50,380

Here’s what I discovered on Oracle’s site related to this …

Solution Summary
Date Created:
04/05/2007 4:46PM PDT
Solution ID: Solution Type: Standard
201039880
Solution Library: PeopleTools

Summary:
E-QR: Query using Outer Joins on SQL server 2005 error
Details: SOLUTION 201039880 : E-QR: Outer Joins on SQL server 2005.

SPECIFIC TO:
Enterprise, PeopleTools 8.48, 8.47.11, Query, SQL Server 2005

ISSUE :
When running queries that contain outerjoins on SQL server 2005, the following errors occur:

SQL error. Stmt #: 5682 Error Position: 0 Return: 8601 - [Microsoft][ODBC SQL Server Driver][SQL Server]The query uses non-ANSI outer join operators ('*=' or '=*'). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptle.

SOLUTION:
Queries using Transact-SQL syntax (using *= and =*), the code will not run on SQL Server 2005 Database. To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN).

Here’s the relevant help text for the new way to create left outer joins in PS 9.0 Query Manager …

1. Find the joining record and then click the Join Record link on the same row as that record.
2. Select Join to get additional fields only (Left outer join).
Note. When you select Left outer join, you must select the last record that you've previously added to the query. If you attempt to join to records other than the last record, an error message appears.

also …
Adding Left Outer Joins Criteria into the ON Clause
If you want to create criteria for the left outer joined record, you can add left outer joins criteria into the ON clause.
To create left outer joins criteria into the ON clause:
1. On the Criteria page, click the Edit button to access the Edit Criteria Properties page.
2. On the Edit Criteria Properties page, select ON clause of outer join from the This Criteria Belongs to drop-down list box.
3. Click OK.

Primary Module: PeopleTools

Environment: CSTST90 (second release of Campus Solutions Test, 6/27/07)

Resolution: We set the compatibility level for current database (CSTST90) to 80, using stored procedure sp_dbcmptlevel. We will ultmately rewrite queries to make use of PeopleSoft query tool functionality, but this workaround will buy a little time.