RE: sort select two fields as one.

I would start by converting the two length fields into one that is expressed in inches. Using your example, you would select pipe from 13 inches to 71 inches, with the specified OD and wall thickness which should make the inquiry pretty easy.

There are a couple of ways to do this but you'll get much better performance by storing the length in a common unit instead of converting them to a common unit on the fly.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of dale janus
Sent: Thursday, July 16, 2009 5:18 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: sort select two fields as one.

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?
---Dale