SQL query gives syntax error after upgrading OpenEdge from 10.2B to 11.7 verison

This article discusses about the syntax error message when an SQl query which worked fine in 10.2B is ahowing syntax error after upgrading the OpenEdge Database to 11.7.x verison.

Feb 18, 2020
Knowledge

Title

URL Name

SQL-query-gives-syntax-error-after-upgrading-OpenEdge-from-10-2B-to-11-7-verison

Article Number

000151392

Environment

Product: OpenEdge Version: 11.7.x OS: All supported platforms

Question/Problem Description

An SQL query is giving syntax error after upgrading the OpenEdge database from 10.2B to 11.7.x version.
SQL query throws the below error message "[DataDirect][OpenEdge JDBC Driver][OpenEdge] ON clause outer join predicate contains invalid column reference. (12480)
SQL clients are not working after the database is upgraded from 10.2B to 11.7.x version.

Steps to Reproduce

Clarifying Information

Error Message

[DataDirect][OpenEdge JDBC Driver][OpenEdge] ON clause outer join predicate contains invalid column reference. (12480)

Defect Number

Enhancement Number

Cause

The problem is occurring due to the wrong join column mentioned in ON Clause for join in the query

Resolution

An external JOIN has precedence over comma (join using comma operator like t1, t2) so the optimizer tries to build external JOIN first and while building optimizer sees that one of ON clause column is from the other table (not part of the JOINed table). Hence optimizer throws the error.
For below SQL query:

select t1.id, t2.id, t2.address_1
from contact t, address t2
where t1.id = t2.id

select t1.id, t2.id, t2.address_1
from person t1
inner join address t2
on t1.id = t2.id

Workaround

Notes

Keyword Phrase

Last Modified Date

3/11/2020 11:25 AM

SQL query gives syntax error after upgrading OpenEdge from 10.2B to 11.7 verison

Files(0)

Was this article helpful?

Was this article helpful?

SQL query gives syntax error after upgrading OpenEdge from 10.2B to 11.7 verison

Information

Related Articles