Kbase P128051: SQL-92: What are the SQL-92 statements equivalent to the 4GL/ABL FOR EACH and FIND statements?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/12/2008 |
|
Status: Unverified
GOAL:
SQL-92: What are the SQL-92 statements equivalent to the 4GL/ABL FOR EACH and FIND statements?
GOAL:
How to make the SQL-92 SELECT statement behave like the 4GL/ABL FIND statement?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1B
FIX:
1. There SQL-92 SELECT statement is 'roughly' equivalent to the 4GL/ABL FOR EACH statement although their behavior is not identical. For example the SQL-92 statement:
SELECT * FROM PUB.Customer;
Will return the all the records in the table Customer. This task is equivalent to:
FOR EACH Customer NO-LOCK.
/* Do something with the record*/
END.
Notice that in both of the above examples, the SELECT and the FOR EACH statements make all the rows of the table available to the client for processing as desired subject to the capabilities, syntax and limitations of the client programming language. If update is intended, additional SQL-92 syntax will be needed like the use of the FOR UPDATE option of the SELECT statement.
2. There is no OpenEdge SQL-92 statement that is 'STRICTLY' equivalent to the 4GL/ABL FIND statement. However, there get behaviors similar to that achieved by the 4GL/ABL FIND statement using the SELECT statement. For example the SQL-92 statement:
SELECT TOP 1 * FROM PUB.Customer;
Will return the FIRST record of the table Customer. This task is equivalent to:
FIND FIRST Customer.
/* Do something with the record*/
Notice that in both of the above examples, the SELECT TOP 1 and the FIND FIRST statements make the FIRST row of the table available to the client for processing as desired subject to the capabilities, syntax and limitations of the client programming language.
Similarly, the SQL-92 statement:
SELECT TOP 1 * FROM PUB.Customer ORDER BY Custnum DESC;
Will return the LAST record of the table Customer. This task is equivalent to:
FIND LAST Customer.
/* Do something with the record*/
Again, both of the above examples, the SELECT TOP 1 and the FIND LAST statements make the LAST row of the table available to the client for processing as desired subject to the capabilities, syntax and limitations of the client programming language.
Keep in mind that the statement:
SELECT TOP 1 * FROM PUB.Customer ORDER BY Custnum DESC;
will return the LAST record relative to the field(s) used in the ORDER BY clause unless there are duplicate such records.