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?
midrange.com
