Re: sort select two fields as one.

IF (Feet < MinFeet OR (Feet = MinFeet AND Inches >= MinInches))
AND ((Feet < MaxFeet OR (Feet = MaxnFeet AND Inches <= MaxInches))

assuming Feet & Inches as DB fields and Min/Max Feet/Inches as whatever yer looking for
put () around the whole thing and prefix with OD & Wall =

Haas, Matt (CL Tech Sv) wrote:

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