Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Error 7520 or 13865 when derived SQL query does not have join columns on the SELECT statement

Error 7520 or 13865 when SQL sub query does not have join columns on the SELECT statement

Column not found/specified (7520)

Column cannot be found or is not specified for query. (13865)

Column "O.CUSTNUM" cannot be found or is not specified for query. (13865)

The following query fails to with above error on OE10.1A02 or higher

SELECT * from pub.customer as c
LEFT OUTER JOIN (SELECT orderdate from pub."order") as o
ON
c.custnum=o.custnum AND
c.salesrep = o.salesrep
WHERE
c.custnum=o.custnum AND
c.salesrep = o.salesrep;

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.1A02 Service Pack
OpenEdge 10.1B
OpenEdge 10.1C
OpenEdge 10.2x

CAUSE:

The derived query creates a virtual table based on the SELECT query. Previous to OE10.1A02, the derived query is creating the virtual table with the all columns regardless of how many column(s) is/are specified on it which is violating the SQL92 standard. After the fix on OE10.1A02 or higher, the derived query only creates the virtual table with the column(s) specified on the query which conforms to SQL92 standard. Thus, the query is not able to find the join column(s) from the derived query.

FIX:

Add all the columns that are being used in the join on the derived query.

Example:

SELECT * from pub.customer as c
LEFT OUTER JOIN (SELECT orderdate, custnum, salesrep from pub."order") as o
ON
c.custnum=o.custnum AND
c.salesrep = o.salesrep
WHERE
c.custnum=o.custnum AND
c.salesrep = o.salesrep;