IBM ISERIES SC41-5210-04 - General Considerations When Creating an Expression in Query for Iseries

Define Result Fields
Type definitions using field names or constants and operators, press Enter.
Operators: +, -, *, /, SUBSTR, ||, DATE...
Field Expression Column Heading Len Dec
MMYYDD_____ DATE(YYDDD)_____________________ ____________________ _____ __
_________________________________ ____________________
_________________________________ ____________________
__________ _________________________________ ____________________ _____ __
_________________________________ ____________________
_________________________________ ____________________
Bottom
_______________________________________________________________________________
Field Text Len Dec
YYDDD Date field in YYDDD format 6 L
Bottom
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
The commands to run the query in example 4 are:
CHGJOB DATFMT(*MDY)
RUNQRY QRY(MMDDYY)
General considerations when creating an expression in Query for
iSeries
Following are some general rules you should use when you are creating expressions:
v Character constants that represent dates are evaluated using the date format stored in the query
definition.
v Your expression can have a single value, or it can use as many operands and operators needed to fit in
the three lines provided on the Define Result Fields display.
v Do not use a result field in select records tests or as a sort field if it may have a division by zero or
overflow result.
v You can avoid dividing by zero by doing one of the following:
– When you select records, either by means of a select/omit logical file or by means of record
selection tests on fields other than the result field, select only records that will not cause a division
by zero.
– If a dividend for the definition of a result field will always be zero, try the following change to the
expression:
Original expression--> FIELD=P/X
Revised expression---> S = .00001
FIELD=P/(X+S)
Note: In the expression S, the number of zero digits before the 1 digit should be two more than the
decimal precision of the original expression.
– If a dividend for the definition of the result field will not be zero when the divisor is, the following
change to the expression prevents an overflow condition.
Original expression--> FIELD=P/X
Revised expression---> S = .00001
FIELD = (P*X) / ((X*X) + S)
Chapter 5. Defining result fields in Query for iSeries 83