Specifying WHERE Parameter in I/O Commands
Fields that Allow SQL Null
A field allowing SQL Null may be used as a key or as part of a where parameter just the same as any other field. It may also be compared to SQL Null. The following example shows how you might retrieve all rows in MYFILE where #MYFLD1 has a real value (not SQL Null).
SELECT(#MYFLDS) FROM_FILE(MYFILE) WHERE(#MYFLD1 *IsNot *Sqlnull)
Note that fields allowing SQL Null may behave differently in where parameters at execution time when they are SQL Null. Refer to Assignment, Conditions, and Expressions with Fields allowing SQL Null for details.
Fields of type BLOB, CLOB, Binary or VarBinary
BLOB or CLOB fields on the file cannot be used in a where condition unless being compared against *SQLNULL. For example:
SELECT(#MYFLDS) FROM_FILE(MYFILE) WHERE(#MYBLOB *Is *Sqlnull)
Any attempt to compare a BLOB or CLOB field on the file to any other value than *SQLNULL causes a FFC error.
Fields of type Float
Floats are an inaccurate numeric type. In the FFC, when a float is compared via *EQ or *NE (or equivalent) to a field, system variable (other than *ZERO), or literal value other than 0, *NULL, or *SQLNULL, a warning message is issued.
Performance
The following applies only to RDMLX on the IBM I and both RDML & RDMLX on non-IBM i platforms.
The use of the following in the WHERE condition will require it to be evaluated in the calling function or component:
- Fields not on the file
- Intrinsic functions such as IsSqlNull
For best performance, only use fields on the file in the WHERE clause. This allows the I/O module to evaluate the condition and return only the rows matching the WHERE, rather than having to return all the rows matching the key provided to the calling function or component.
For even better performance, only use real fields on the file (not virtual fields). If the I/O command is handled in SQL, this will minimise the number of rows to be returned.