Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

OpenEdge 10.0A

SYMPTOM(s):

Why SQLExplorer ignoring INDEX clause in a SELECT for displaying data in the index order.

CAUSE:

Bug# 20040212-026

CAUSE:

* You need to start a server for the sports2000 database
* Use the ODBC Data Source administrator and define the ODBC connection to the sports2000 database
* In OpenEdge 10, bring up the data dictionary and add a new index on the customer table, call it nameb. Use the field customer and put it in descending order.
(With all that, if you run a 4GL procedure, you will see that it uses the new index:
FOR EACH customer USE-INDEX NAMEb:
DISPLAY NAME.
END.
* Bring up the SQL Explorer
* Connect to the sports2000 database
* Enter in the following SQL statement:
select name from pub.customer with (index (nameb) ) order by name;
The output from this example is as if you did not specify the index nameb. What is expected was for the records to be retrieved and be in descending order.

FIX:

This is not a bug at all.

If you want the results of a SQL query in a particular order, then you do that by adding an order-by clause. If you do not, then the query optimizer is free to do as it wishes and can return results in whatever order.

4GL and SQL query semantics are different and specific behaviour in one and not in the other does not mean a bug in the other.

WITH is used to present one or more tables hints.
A table hint is used to create an excecution plan for the query. The SQL Server query optimizer will automatically choose the best execution plan therefore this option is rarely used.

INDEX Keyword are used to present a list of indexes which are used to speed up the access of data not to display the query in a specific order as in 4GL.

Therefore in this example the correct syntax to display the result in a specific order will be:

select name from pub.customer with (index (nameb) ) order by name desc