Re: Load a subfile with clients selected by user

Well, David, something is going to read those extra tables, whether you go with embedded SQL or native - either you or the SQL engine. With native, I'm suggesting filling an array, then sorting it, then filling the subfile from it - probably. I do this with some maintenance screens that have mixed sorting, gets me what I want - and that is even using SQL!!

Your description sounds simple enough - again, SQL has to do something similar.

read Adresss-file;
dow not %eof(Address-file);
Chain address Client-address-file;
if %found(Client-address-file);
chain client Client-file;
if %found(Client-file) and [name-matches];
[do whatever you need to do - add data to subfile, or to array, sort later];
endif;
endif;
read Address-file;
enddo;

And yes - this is a priming read - there is another way, so no comments required on that front. ;-)

As to a logical on client name, well, if you did the innermost chain on that logical, you'd have to compare the client. Maybe you have a logical on client AND name - then the innermost chain would be

chain {client : name} Client-file;

Then your if %found(Client-file); is all you need to determine whether to continue processing.

This seems simple - easily maintained - and goes directly to the files. And if you do not need the data in the Client file, you could use a setll instead of the chain - this would not require the IO to retrieve the actual data, only the IO on the index (the logical file). Remember, when reading through an index, you have 2 IOs - one for the logical file, one for the physical file it points to - that is a generalization, of course.

Anyhow, that seems easy enough. Am I missing something? With SQL you need a 3-way JOIN - it might work well but maybe not. And if you put a sort on it by name, it still might not use that name index, because it will have to sort the result, probably.

Mock up the SELECT and run it in iSeries Access into Visual Explain and see what it does.

Vern

David FOXWELL wrote:

Vern,

I'v been thinking it over.
I'm kind of wishing I'd never discovered embedded SQL. I think that the native solution would be complicated, less efficient and more difficult to modify.

For example, if a user wants all clients in Bradford with a surname beginning with F :

Read Bradford addresses from address file,
For each address,
Read client numbers associated with that address from client/address file
For each client at that address
Chain to client file and compare name.

In this case, the index on client name will never be used.

If I use the SQL solution, I'll effectively just read from one file, ie a cursor that I'd have created.



* From: Vern Hamberg <vhamberg@xxxxxxxxxxx>
* Date: Fri, 26 Jun 2009 09:22:25 -0500

David

If you go SQL<http://archive.midrange.com/rpg400-l/200906/msg00445.html#>, you would be better off doing it all with SQL - I'd say use CHAINs or SETLL into the address file - if you have a logical over postcode or the other fields, SETLL is enough to test existence and is very inexpensive in IO and CPU<http://archive.midrange.com/rpg400-l/200906/msg00445.html#>.

You need several logical files, but you do, anyhow, to get good performance with SQL.

SQL is not the answer for every situation, IMHO.

HTH
Vern

David FOXWELL wrote:
Hi, I have this tricky ( for me) problem.

We started off with a screen where a user<http://archive.midrange.com/rpg400-l/200906/msg00445.html#> could enter part of a client's name and a subfile would then be shown with those clients matching the name entered. We have a PF that contains clients that can be persons or companies. In that PF either client_name or company_name will exist for a client, but never both. A logical exists on company_name and another on client_name. Depending on the user input, the program decides which logical to read from. Recently, a third logical was created when the users wanted to search by maiden name.

Now, I've been asked to take into account zip<http://archive.midrange.com/rpg400-l/200906/msg00445.html#> or postcode, town and date of birth into the equation. As the addresses are in an adress file with an intermediate client/address file needed to get from the client to the address, it is obviously no longer just a question of adding another logical and reading from that.

It looks like a case for SQL. It probably should have started out that way but at the time embedded SQL was unknown ( to us ).

I was thinking of leaving everything intact, as I don't want to completely rewrite, but using SQL whenever one of the new fields gets used by the user. In that case, I'd use the user input to create cursors<http://archive.midrange.com/rpg400-l/200906/msg00445.html#> and use these to populate the subfile. At the moment the subfile is charged one page at a time and pagedown or up always reads from the PF.

If there are any flaws with this choice, maybe someone would let me know. I'm a little worried having never used a cursor to go backwards and forwards and to populate a subfile. The program will be continually opening and closing cursors with the same name. I think I need to manage when a user changes a field so that I don't close the cursor unneccessarily. Are there any issues to look out for when opening a cursor, say C1 for SMITH then closing C1 and reopening it for JONES of Birmingham, etc,etc.

I guess what I'm trying to say is am I at risk of any bad surprises given that I have not a huge amount of experience with such use of SQL?