Re: RPG400-L Digest, Vol 8, Issue 888

Hi,

need help with my sql stored procedure.

I'm hoping someone can help clear me up.

My problem is I am selecting a policy from file COVMST that has only two
records for my selection.
other files I'm gathering information on relate to this record are:

INSNAM which has the insured first and last name.
INSADDR which has the address information for the insured.
ACCTMST which has the name of the credit union

INSNAM file has 3 name records that match the selection criteria, I only
need one of them.
INSADDR file also has 3 address records that match the selection criteria,
and I only need one of them

What seems to be happening is my result ends up trippled, because of the
additional name and/OR address records.
how can I change my sql to only have name and address for the two records
from the COVMST.
My result should only have two records. I'm actually getting 06, I think
because there are 3 records found in the INSADDR
FILE for the same policy. This happens because additional records are
added if there is a claim.

Also if there isn't a record selected from the address file it still needs
to display the information found
from records selected.

I know the sql is ugly,

I left off the address information for security reasons

co acct policy cov Desc Eff Date Expire
Name
AN 1007 6009156 0 104 SGLNET 20011221
20091221 GONZALEZ
AN 1007 6009156 0 150 R14 20011221
20091221 GONZALEZ
AN 1007 6009156 0 104 SGLNET 20011221
20091221 GONZALEZ
AN 1007 6009156 0 150 R14 20011221
20091221 GONZALEZ
AN 1007 6009156 0 104 SGLNET 20011221
20091221 GONZALEZ
AN 1007 6009156 0 150 R14 20011221
20091221 GONZALEZ

SET PATH "QSYS","QSYS2","SYSPROCS","SYSIBMADM";

CREATE PROCEDURE &LIB/STR006SP1 (
IN COCD CHAR(2) ,
IN AGNT CHAR(10) ,
IN POLM CHAR(10) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC &LIB/STR006SP1
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC &LIB/STR006SP1
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX
CLOSQLCSR = *ENDMOD ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
RDBCNNMTH = *RUW ,
SRTSEQ = *HEX
BEGIN

-- SET CURRENT DATE FOR USE IN SELECTING NONE EXPIRED
-- POLICIES.

DECLARE WRKDAT NUMERIC (8,0);
DECLARE USER CHAR (10);
SET WRKDAT = (YEAR(CURRENT DATE)*10000) + (MONTH(CURRENT
VALUES SESSION_USER INTO USER;

-- SET LIBRARY LIST

IF USER = 'CRPRDQUA' THEN
CALL CRPRDLIBTS/STR126CL;
ELSE
CALL CRPRDLIB/STR126CL;
END IF;