7.83.2 SELECT Comments / Warnings
SQL does not handle all of its table operations in the same manner as file operations on the IBM i. Here are some important points which you should be aware of:
- The SQL based SELECT operation may select all the matching rows at the time it is first executed (into a temporary table) and then proceed to process the selected set of rows (one by one).
This style of processing may cause functional changes between IBM i and Visual LANSA applications where a SELECT loop actually inserts or updates rows, as it goes, so that they become part of the set of "selectable" rows.
Under IBM i such rows would be processed by the SELECT loop. Under Visual LANSA they may not, because they were not part of the initially selected set.
This style of processing would be quite strange under IBM i because it runs a very real risk of infinite loops, but this processing difference should be noted and you should avoid this style of processing.
- DO NOT break SELECT loops with GOTO commands as this may leave the SQL cursor open. You should use the LEAVE RDML command to exit SELECT loops instead.
- DO NOT under any circumstances branch into the middle of a SELECT loop. This is an unnatural coding technique that will produce unpredictable results on any platform.
- For similar reasons to the previous points, changing the value of selection criteria within a SELECT loop may produce platform variant results.
Consider this SELECT loop where SALARY is a column in the SQL table and REQSALARY is some sort of selection value:
SELECT FIELDS(...) FROM_FILE(...) WHERE('#SALARY <
#REQSALARY')
.......
.......
.......
CHANGE #REQSALARY ('#REQSALARY * 1.1')
ENDSELECT
- This is not a well written piece of logic and it may produce differing results between platforms.
- Visual LANSA evaluates and selects according to the values at the time that the SELECT is first executed. Do not change the value of selection criteria after they have been established or unpredictable results may occur.
- The SQL-based SELECT operation with a WHERE condition involving only "real" fields in the named file selects only the matching set of rows from the file. This means that after read triggers will not be invoked for those rows not matching the WHERE condition.
This may be functionally different to RDML on the IBM i where all rows are read before testing the WHERE condition
If the WHERE condition involves any fields that are not "real" fields in the file then the processing will be identical under Visual LANSA and IBM i (RDML and RDMLX).
- Generic search where the search string contains the '%' character may not act the same as on the IBM i.
Refer to the SQL LIKE predicate in the appropriate SQL Reference manual.
- Use of the SELECT option *STARTKEY is not recommended as it may have performance implications when using SQL requests to emulate the positioning of a "file cursor".