If the use of Query for iSeries is a part of your daily iSeries chores, then a new function introduced in V5R3 could spell big gains. However, the documentation for the function did not make it into the Query for iSeries Use manual. The new function is Length, and it returns the length of its argument.
The details of Length
The Length function is similar, if not identical, to the Length function in SQL. The function has one parameter, which can be a field name or an expression; the function then returns the length of that field or expression.
The parameter can be of any valid type, character or numeric. Date, time and graphic types are also supported.
In most cases, the result (i.e., the Length's return value) is the buffer length of the field (or the expression if it were in a buffer). Specifically, the possible return values are as follows:
- Small integer: 2.
- Large integer: 4.
- Big integer: 8.
- Single-precision float: 4.
- Double-precision float: 8.
- Packed decimal numbers with a precision of (i.e., total number of digits) p: the integral (i.e., whole number) part of (p/2)+1.
- Zoned decimal numbers with precision p: p.
- Fixed-length strings: the length of the string including blanks.
- Variable-length strings: the length of the actual string (trailing blanks not included).
- Time: 3.
- Date: 4.
- Timestamp: 10.
- Row ID: 26.
If the field or expression passed as the parameter supports null values, then the result of the Length function will support null values. Moreover, if the argument is null, then the result will be null.
How can you use the new Length function
Many situations can call for the new Length function. I run into them frequently, especially when doing data transfers to other systems. Electronic data interchange (EDI) systems are a good example. Many EDI systems require that a length attribute precede each field of data. The Length function makes this a simple chore if you prepare data using Query for iSeries.
When using the Length function with packed decimal data, you must take care to avoid mistakes interpreting the results. For example, a 15.2 packed decimal field will have a length of 8 (int(15/2) + 1). A 14.5 packed decimal field also has a length of 8.
When doing data exchanges, packed decimal data is usually not acceptable because most platforms don't support it, so you will be converting it to other data types. For example, floating decimal point data is often acceptable. Sometimes you may just want to convert packed decimal data to character string data. For example, a 9.2 packed decimal field named Amount could be converted to character using the following Query function:
Digits(Amount)
And you could calculate the length of that resulting character string using the new Length function:
Length(Digits(Amount))
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.
