Kbase P4329: Different field order when executing a 4GL query as opposed to a SQL-92 query
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/08/2010 |
|
Status: Verified
SYMPTOM(s):
Different field ORDER when executing a 4GL query as opposed to a SQL-92 query
SQL-92 does not display the table columns in the same order as 4GL
Executing a "SELECT * <table>" statement from a SQL-92 client.
Executing a "FOR EACH <table>: DISPLAY <table>" statement from a 4GL client.
FACT(s) (Environment):
Progress 9.1x
All Supported Operating Systems
OpenEdge 10.x
CAUSE:
Bug# OE00071591
CAUSE:
SQL-92 has the goal of being standards compliant and independent of the 4GL. The SQL standard does not specify the order of columns in the result set for "select *". SQL-92 does not want its results to be determined by 4GL metadata like _Order.
SQL-89 has the goal of being like the 4GL, so it uses the _order value
for _Fields to give the order of the columns for "select *".
When a 4GL statement is used it uses _Field._Order.
When a SQL-92 SELECT statement is performed, the query uses _Field._field-physpos which corresponds to SYSCOLUMNS.ID to define the order.
This is not a bug, but is expected behavior.
FIX:
You can work around this issue using the following methods:
1. Explicitly list the fields in the desired order within the SQL query.
- OR -
2. Create a VIEW in SQL-92 which specifies the fields in the same order as 4GL. Then query the VIEW instead of the table.