Re: sort select two fields as one.

I think that should have been
IF (Feet > MinFeet OR (Feet = MinFeet AND Inches >= MinInches))
AND (Feet < MaxFeet OR (Feet = MaxFeet AND Inches <= MaxInches))

Personally, I like to leave the operator alone and change the order of
the operands

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

Also to the OP, I'd also recommend you forget about OPNQRY and use embedded SQL.

Particularily if you want the selection criteria to accept variables.
With embedded SQL all you'd need to do with the above is make min/max
feet/inches variables like so

select fld1, fld2, <...> into :myDS
from myfile
where (:MinFeet < Feet OR (Feet = :MinFeet AND :MinInches <= Inches))
AND (Feet < :MaxFeet OR (Feet = :MaxFeet AND Inches <=
:MaxInches))
order by fld1, fld2;

Trying to use variables in OPNQRYF is more difficult. Though at least
in this case, you'd be dealing with numeric values (that you'd have to
convert to character before concatenating to the QRYSLT statement.)


HTH,
Charles

On Thu, Jul 16, 2009 at 6:03 PM, Denny Ray<DennyRay@xxxxxxxxxxxxx> wrote:

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

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.