Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1161
  • Last Modified:

Cursors and Stored Procedures for iSeries - For use with Crystal Reports

I want to set up a stored procedure that will be used by Crystal Reports.  The procedure that I've created works but I get a data conversion error from Crystal.  I think I'm on the right track but I'm stuck.  Please let me know what I need to do to create this procedure properly.

Thanks,
H

CREATE PROCEDURE Crys_File.usp_Cmpl02()

--Stored procedure used by Crystal Report - Licensed Agents by Territory
--used by Compliance Dept

RESULT SET 1
LANGUAGE SQL MODIFIES SQL DATA

BEGIN
      DECLARE C1 CURSOR WITH RETURN FOR
            SELECT
                  B.AGTERR,
                  D.TRDESC,
                  C.ACCT,
                  B.AGNAME,
                  C.DLRPRN,
                  A.LSTNME,
                  A.FSTNME,
                  A.LICNSE,
                  A.GROUP,
                  A.ONFILE,
                  A.TRAINOUTLN,
                  A.STMNTS,
                  A.SPEC_POL,
                  A.RNWDTE
            FROM SEFILES.LCAGENT A
                  JOIN SEFILES.LCAPRD C ON
                  (A.LICNSE = C.LICNSE) AND
                  (A.LSTNME = C.LSTNME) AND
                  (A.FSTNME = C.FSTNME)
                  JOIN SEFILES.ACCTMST B ON
                  (C.ACCT = B.AGACCT)
                  JOIN SEFILES.ACCTTER D ON
                  (B.AGTERR = D.TRTER)
            WHERE (A.TRMDTE = 0 AND B.AGSTAT IN ('A', ' ')
                  AND C.TRANS <> ('T') AND A.STAT_90 = 0) OR
                  (A.TRMDTE = 0 AND B.AGTERR = 90 AND C.TRANS <> 'T');
                  
                  OPEN C1;
                  
END;
0
Sixpach71
Asked:
Sixpach71
  • 2
  • 2
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
The only thing that stands out to me is that you forgot to set the result set.

e.g.

OPEN C1;
SET RESULT SETS CURSOR C1;

HTH,
DaveSlash
0
 
Sixpach71Author Commented:
The procedure compiled but when I called it, I recieved several error messages:

1.  [SQL0802] Data conversion or data mapping error.  
2.  Message: [SQL0466] 1 result sets are available from USP_CMPL01 in CRYS_FILE.  Cause...Procedure USP_CMPL01 in CRYS_FILE was called and has returned one or more result sets.  Recovery...none.

Any ideas?


0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Well, the second message just says there's a result set (which is good).  I don't know why you'd get the first message.

Ty trimming your SELECT down until it's trivial. Does it still work?  If so, keep adding the complexity back until it breaks.

e.g.
   SELECT
               A.LSTNME,
               A.FSTNME,
   FROM SEFILES.LCAGENT A
;

If the trivial case doesn't work, then you know you have bigger problems than just your SQL.

HTH,
DaveSlash
0
 
Sixpach71Author Commented:
I tried a simple select and it worked so, as you have suggested, I'll rebuild until I break it.

Thanks for your help.

H
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now