Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Data for field is wrong if PRO_ELEMENT function is used in GROUP BY clause in a SQL-92 query

Field contains more data than expected if PRO_ELEMENT function is used in GROUP BY clause

Field has contains a value for each record when PRO_ELEMENT function is used in GROUP BY clause

Field has contains a value for only the first record when column alias for the PRO_ELEMENT function is used in GROUP BY clause

FACT(s) (Environment):

Using PRO_ELEMENT function, providing column alias, in SELECT clause
Using the same PRO_ELEMENT function in the statement's GROUP BY clause
Query refers to same table twice via LEFT OUTER JOIN:

SELECT
PRO_ELEMENT("T2"."amount",1,1) "firstAmount"
FROM "PUB"."table1" "T1"
LEFT JOIN "PUB"."table1" "T2" ON
"table1"."field1" = "T2"."field1" AND
"table1"."field2" = "T2"."field2" AND
"table1"."field3" != "T2"."field3"
WHERE
"table1"."field1" = 1 AND
"table1"."field2" = 'A' AND
"table1"."field3" = 12345
GROUP BY
PRO_ELEMENT("T2"."amount",1,1)
OpenEdge 10.1C
OpenEdge 10.2A
All Supported Operating Systems

CAUSE:

Bug# OE00182091

FIX:

Use the column alias in the GROUP BY clause instead of the PRO_ELEMENT function. For example:

SELECT
PRO_ELEMENT("T2"."amount",1,1) "firstAmount"
FROM "PUB"."table1" "T1"
LEFT JOIN "PUB"."table1" "T2" ON
"table1"."field1" = "T2"."field1" AND
"table1"."field2" = "T2"."field2" AND
"table1"."field3" != "T2"."field3"
WHERE
"table1"."field1" = 1 AND
"table1"."field2" = 'A' AND
"table1"."field3" = 12345
GROUP BY
"firstAmount"