RE: Load a subfile with clients selected by user

-----Message d'origine-----
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de rob@xxxxxxxxx
Give me an example of the multiple 'where' selection criteria
that the user may enter.

Rob, a little late here,

User may enter :
Surname, christian name, maiden name, date of birth for the client.
Also town and Zipcode for the address.

One might enter something like :
Smith, London,
then David, zipcode12345,

then 24/12/1980 etc

The three tables implicated have around 1 000 000 records a piece.

Thanks

Rob thanks,

I feel this is the way to go, but the question of the number
of cursors is a difficult one for me.

I thought I'd have 2, one for the event of the user entering
only the client information and another for when address
information is entered.

My select criteria would always be the same ( governed by the
subfile to fill), the from file will be the client file or
the address file joined to the client file.

I'd have to build the WHERE criteria depending on the user
data entered.

So, I <think> I'd have a function like getClientsByClientInfo
and getClientsByAddress. Each would create a cursor depending
upon the WHERE criteria received. If this criteria changed,
I'd close and recreate the cursor.

Does that sound reasonable?







* From: rob@xxxxxxxxx
* Date: Fri, 26 Jun 2009 09:52:46 -0400

In summary, you need a good skeleton of a SQLRPGLE program
that processes a subfile, backwards and forwards.

And the other question is: Are you better off using multiple
cursors<
http://archive.midrange.com/rpg400-l/200906/msg00442.html#>
(for the different selection criteria) or using "prepared"
statements? Dynamic
sql<http://archive.midrange.com/rpg400-l/200906/msg00442.html#

or not?

There are better 5250 programmers out there so I'll hope you
get a good template from them.

As far as the cursors go, study this:

Step 1: Build a rpg variable that has all this in it.
MySqlStmt =
Declare C1 cursor for
Select t1.col11, t1.name, t1.col13, t2.zip<
http://archive.midrange.com/rpg400-l/200906/msg00442.html#>,
t3.city from table1 t1 join table2 t2 on t1.col12 = t2.itskey
join table3 t3 on t1.col13 = t3.itskey where t1.name between ? and ?
and t2.zip between ? and ?
and t3.city between ? and ?
order by ...
Comment: The question marks are called "parameter markers".
More on this later...

Step 2: Prepare a sql statement from this variable exec sql
Prepare Stmt1 from :MySqlStmt;

Step 3: Declare the cursor from the prepared statement.
exec sql Execute Stmt1;

Step 4: Open the cursor. Pass in variables into the parameter markers.
If you only want a single value for "t2.col2a" then pass the
same variable into the left and the right of the and.
exec sql Open c1 using :FromName, :ToName, :FromZip, :ToZip,
:FromCity, :ToCity;

Then you do your fetches and whatnot.

Study also:
- Soundex http://preview.tinyurl.com/sqlsoundex
- like http://preview.tinyurl.com/sqllike


Rob Berendt
--
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.



--
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.