Formatting output in LANSA

Date:Archived
Product/Release:LANSA for the AS/400
Abstract:Sometimes, after creating all necessary logical files when writing an application on the AS/400, a "special" requirement arises for some other more specific or generic searching requirement. Apart from LANSA's FETCH, SELECT, SELECT_SQL commands LANSA also supports the AS/400 OPEN QUERY feature via LANSA's OPEN command.
Submitted By:LANSA Technical Support

The following examples are some quite advanced options available from OPEN QUERY. See LANSA's manuals for other "simpler" options.

%RANGE

With %RANGE it is possible to select all records within a specific range.

Example:

QRYSLT('NUMBER *EQ %RANGE(1 4)')

All records where the field NUMBER is filled with the value 1, 2, 3 or 4 will be selected.

%VALUES

With this option it is possible to pickup one or more records which are equal to the given values.

Example:

QRYSLT('NAME *EQ %VALUES("JOHNSON" "HOUBA")')

All records where the field NAME is filled with JOHNSON or HOUBA will be selected.

%WLDCRD

With the wildcard option it is possible to select records were a field is filled with a specific string. It is possible, for example, to select all records where the field Zip Code begins with the string 29.

Example:

QRYSLT('NAME *EQ %WLDCRD("H__BA*")')

The first character of the field NAME has to be an H, the second and third characters are unimportant, but character 4 has to be a B, character 5 an A, all the rest of the characters (this is the * sign) are unimportant.

Example:

QRYSLT('NAME *EQ %WLDCRD("V*N*")')

The name must start with the letter V and somewhere in the name the letter N must exist.

%SST

With the substring option it is possible to test the some characters of a field.

Example:

QRYSLT('%SST(CITY 2 1) = %VALUES("A" "M" "W")')

When the second character of field CITY is filled with the value A, M or W, the record will be selected.

%XLATE

All CITY's which contains the string DAM will be selected. When using the XLATE function, it doesn't matter if the characters in the database are in uppercase or lowercase.

Example:

QRYSLT('%XLATE(CITY QSYSTRNTBL) *CT "DAM" ')

The example also uses one of the translation tables of the AS/400. The result of the translation table QSYSTRNTBL is, that the selected fields CITY are always presented in uppercase (also when the characters in the database are lowercase).