For RDMLX tables, the use of Select/Omit Criteria is not recommended. Instead, use the WHERE parameter to limit the data retrieved.
Select/Omit Criteria can be entered to specify details of any records that are to be either selected or omitted from an index. If a record is omitted, it means that although the record is present in the physical table, it is seemingly invisible in the index. A record will be omitted if it fails the selection test or passes the omission test. The criteria are entered as a series of statements.
A record can be omitted for 2 reasons:
Select/omit criteria is a powerful facility but should be used with some caution because the effect of making some records in the physical table "invisible", when accessed via certain indexes, can cause confusion among some users of the system.
Select/omit criteria are entered as a series of select/omit statements. When both SELECT and OMIT are used the order of the statements is important. The statements are processed in the order specified. If a record matches the criteria of a statement it is selected or omitted as specified and following statements are not tested.
Note that the type of record select/omit logic used by LANSA is identical and totally compatible with that used in IBM i DDS (data description specifications). In fact the select/omit criteria specified under LANSA are directly translated into DDS select/omit statements.
The only difference is that, under LANSA, the full words SELECT or OMIT are used. In DDS, they are indicated by entering an "S" or "O" into column 17 of the input form. Other than this difference, they are absolutely identical in specification logic.
The type of checking done by LANSA to the select/omit statements entered is relatively unsophisticated. As such the possibility arises of invalid select/omit statements being accepted into the system.
If this happens the problem will manifest itself the first time that an attempt is made to create the index. The LANSA job will fail and messages on the job log will indicate that the index failed to create successfully. If this happens, examine all source listings named QDDSSRC produced by the job.
Locate those which contain errors. If the errors indicate problems with the DDS select/omit statements generated by LANSA then delete and redefine the associated index so that the LANSA select/omit statements are acceptable to the DDS processor.
Since the LANSA select/omit statements are totally compatible with those used in DDS it would be beneficial to read the sections in the IBM supplied manual "Data Description Specifications" that relate to select/omit processing and the select/omit keywords CMP, COMP, RANGE, VALUES and ALL.
Select/Omit Criterion in the Technical Reference Guide