Re: sort select two fields as one.

dale janus wrote:

I have a simple inventory list program. It is so simple it uses
the cycle.

I am trying to modernize it and I am running into difficulties on
selecting (and sorting) records in CL using OPNQRYF. I am not
opposed to using SQL, but I have only played around with it a
little bit and am not very familiar with it.

Our product is steel pipe and we need to select on 4 fields.
Outside diameter (OD) wall, feet and inches. If I try to select
a range from 2 feet 1 inch to 5 feet 11 inches, %range will grab
all feet that are between 2 and 5, and then all inches that are
between 1 and 11. This is not what I want.

I need to select lengths by treating feet and inches as one
field. I can create a logical file view with dds and
concatenate the two fields into one. But I cannot do it for od
and wall because they have decimals.

I was planning on digging into the arcane rites and rituals of
MAPFLD, as well as seeing if Query Manager could create valid SQL
statements for me. (I've used Query Manger to create SQL
statements in the past.) But I thought I'd ask here first if
there was a better way. I know I could probably do the select in
RPG, but I'm not sure about the sort part. Plus I'm used to the
old ways of getting the file the way you want it, then print.

Any suggestions?

Any such range can be expressed generically as three ranges; albeit some special cases can be done in two or even one range test. For example the range from 2 feet 6 inches to 5 feet 10 inches is the equivalent of the three ranges:

/* assuming integer numbers */
2 feet and inches between 6 and 11 inches
between 3 and 4 feet [irrespective of inches]
5 feet and inches between 0 and 10 inches

qryslt(' ... +
*and ( ( FEET = 02 *and INCH = %range(06 11) ) +
*or ( FEET = %range(03 04) /* INCH is moot */ ) +
*or ( FEET = 05 *and INCH = %range(00 10) ) )')

Consider using the input of the lower and upper bounds of both the feet and inches to dynamically build the above three-part range test for the query selection, instead of trying to modify the key or using an expression to map the values for an alternate key. With this method the existing key(s) on FEET and INCH can be utilized by the query engine.

Regards, Chuck