RE: Load a subfile with clients selected by user

User may enter information from one only of the following groups :

1.CompanyName,
2.ClientName, ClientSurname, ClientDateofBirth, ClientMaidenName
3.ClientNumber

For cases 1 and 2, the user may also enter AddressTown and/or AddressPostCode.

So I need to be able to generate, for example :

Select ClientData from clients
Where clientName = userentered name and
clientSurname= userenterde surname etc

Or :

Select ClientData from clients join clientsAddresses on ... Join addresses on ....
Where addressTown = userentered town
clientBirthDate = userentered birthdate etc


Thanks, hope that's clear.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de rob@xxxxxxxxx
Envoyé : mardi 30 juin 2009 14:31
À : RPG programming on the IBM i / System i
Objet : Re: Load a subfile with clients selected by user

Give me an example of the multiple 'where' selection criteria
that the user may enter.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
David FOXWELL <David.FOXWELL@xxxxxxxxx>
To:
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date:
06/30/2009 04:55 AM
Subject:
Re: Load a subfile with clients selected by user Sent by:
rpg400-l-bounces@xxxxxxxxxxxx



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.