× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Sharon,

I too would use dynamic sql. But I also suggest creating single column Encoded Vector Index over each selectable field over the table. Since you do not know what the user wants before the program runs, the EVI should help with selecting the data for you.

So if the one user wants to search for customer name = 'SCHUTTE' and total sales = $100. You would have an EVI over customer name and total_sales. The dynamic sql statement would be
Select * from table where customer_name = 'SCHUTTE and total_sales = 100;

Another user might want all customers in the state of ohio and city of columbus. With EVI over both fields, you should be able to select these quickly
Select * from table where state = 'OHIO' and city = 'COLUMBUS';

Etc.

Look up EVI on the IBM website for more information.

Another option would be to ask about normalizing the table. If there's repeated information in the table, it should be in a table have its own. For example customer name, city, state, etc should all be replaced with customer number and joined to the customer master table by customer number.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 8:11 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Reduce large amount of logicals in SUBFL pgm, take in another direction

Good morning,

new job and given the assignment to revamp a screen pgm that the whole
company uses to make it more efficient, less CPU.

There are 12 different fields a user can fill in to search for the info they
want. Plus there are fields they can check off for other criteria. Total
about 25 different fields on the screen.

Currently, there are about 80 logicals that are used for all the different
combinations of fields to search on. The first SUBR of the pgm does various
validations against all the possible fields, then depending on that result
does a SETLL for the appropriate logical. Then a second SUBR checks the
fields again and depending on that does a READ, READE or READP against the
logical that was staged.

They want to select the data the first time through to a type of work file,
then as more information is filled in by the user, query or read against
that work file, drilling down further for each field that is populated by
the user. They want to reduce CPU and the amount of logicals.
I was thinking of doing a query against the work file as it gets drilled
down.

Wondering how the super users that use this forum would take this on. Any
input would be appreciated.


Thanks.


Sharon





Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com
--
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.

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.