Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

SQL: Incorrect data columns in VIEW containing a JOIN clause

An extra unnamed column may be created in a view definition's metatadata when the view contains a JOIN

The following statement generate an extra UNNAMED column in the created View:

CREATE VIEW test.customer1 AS
SELECT
pub.customer.*
FROM
pub.customer
JOIN
pub.order ON pub.customer.custnum = pub.order.custnum

Executing the following query against the view created above lists an unnamed extra column:


SELECT
*
FROM
sysprogress.syscolumns
WHERE
tbl = 'customer1'
ORDER BY
ID

The above query produces the following erroneous output. Notice the first column that has no name:
ID COL TBL OWNER COLTYPE WIDTH SCALE NULLFLAG DFLT_VALUE CHARSET COLLATION
2 CUSTOMER1 TEST smallint 2 0 Y undefined basic
3 CustNum CUSTOMER1 TEST integer 4 0 Y undefined basic
4 Name CUSTOMER1 TEST varchar 60 0 Y undefined basic
...
...

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.2A
OpenEdge 10.2B

CAUSE:

Bug# OE00196994

FIX:

None at this time. A workaround is change the query to use FROM / WHERE instead of JOIN. For example:
CREATE VIEW test.customer2 AS
SELECT
pub.customer.*
FROM
pub.customer, pub.order
WHERE
pub.customer.custnum = pub.order.custnum
Executing the following query against this 'Customer2' View produces the correct expected output:
SELECT
*
FROM
sysprogress.syscolumns
WHERE
tbl = 'customer2'
ORDER BY
ID
The above query produces the following correct output:
ID COL TBL OWNER COLTYPE WIDTH SCALE NULLFLAG DFLT_VALUE CHARSET COLLATION
2 CustNum CUSTOMER1 TEST integer 4 0 Y undefined basic
3 Name CUSTOMER1 TEST varchar 60 0 Y undefined basic
...
...