RE: Efficient Code

SQL!

I would build the SQL Statement dynamically. Something like:

Sql = 'select <the fields you need> from thefile 
        where key1=key1value 
        and key2=key2value 
        and key3=key3value and '

If Field1Value <> ''
        Sql = Sql + 'Field1 = Field1Value and '
End IF

If Field2Value <> ''
        Sql = Sql + 'Field2 = Field2Value and '
End IF

...

And then open and read the SQL. 

Keep in mind that using SQL does not negate the need for a good supply if
logicals. The SQL will work with out any logicals, but if you want it to
perform well you'll need a good supply of logicals with the "correct" keys.
"Correct" is dependent on the data you have in each of these fields. 

-Walden

------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
(208) 692-3308 eFax
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----Original Message-----
From: Chris_Bougher@xxxxxxxxxxxxx [mailto:Chris_Bougher@xxxxxxxxxxxxx] 
Sent: Tuesday, February 17, 2004 3:54 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Efficient Code

What would be the most efficient way to get records from a large file where
the user can enter selection criteria for as many as 16 fields. i.e.

Key1 is required
Key2 is required
Key3 is required

Field1 is optional
Field2 is optional
Field3 is optional
Field4 is optional
Field5 is optional
Field6 is optional
Field7 is optional
Field8 is optional
Field9 is optional
Field10 is optional
Field11 is optional
Field12 is optional
Field13 is optional

The file currently has over 9 million records and growing.

The file has a record length of almost 300 bytes.

The file currently has 51 logicals but none have the keys I need in the
right sequence.

The screen requires that values be entered for the key values but the
others may  or may not have values entered.

I am not sure if SQL would be the best or making a few logicals over the
file with only the keys and checking the record against the entered data
would be better.

Any opinions?

Thanks in Advance.
Chris Bougher



_______________________________________________
This is the RPG programming on the AS400 / iSeries (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.