|
midrange.com
I had to change the PREPARE & EXECUTE statements. I changed 'SQLCMD' to
'SQLCMD1' for REPORT1, 'SQLCMD2' for REPORT2, 'SQLCMD3' for REPORT3. I'm
not sure why I could use and reuse the PREPARE & EXECUTE statements the
first time around and not the second time around.
---------------------- Forwarded by Frank Kany/IS/CORP/AverittExpress on
04/25/2005 11:51 AM ---------------------------
Frank Kany
04/25/2005 11:29 AM
To: RPG programming on the AS400/iSeries <rpg400-l@xxxxxxxxxxxx>
cc:
Subject: RE: Embedded SQL Problem (Document link: Frank Kany)
The program still crashed on the second report. I tested each report
seperately and they all ran fine. When I tried running all 3 reports, the
program crashed on the second report.
<SRamanujan@xxxxxxxxxxxxxxxxxx>@midrange.com on 04/25/2005 10:28:41 AM
Please respond to RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces+fkany=averittexpress.com@xxxxxxxxxxxx
To: <rpg400-l@xxxxxxxxxxxx>
cc:
Subject: RE: Embedded SQL Problem
Hi Frank,
Before I create the scenario, I would like you to please try this. As
per definition, a temporary table is created for a process and dropped.
You call to each report 1,2 or 3 is within the same process (I assume).
In which case, you are trying to create the same table over and over
again without dropping it. (I don't know if you can explicitly drop a
temp table). So please try change the declares temp tables to 3 unique
names on each reports. Since they will be dropped after the process,
this will not be a separate over head. (I would rather consider
creating a view for the reports instead of the overhead of creating temp
tables but that would be another discussion.)
Please let us know how it went.
Thanks,
Sudha
Definition of a temp table:
The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared
temporary table for the current application process. The declared
temporary table description does not appear in the system catalog. It is
not persistent and cannot be shared with other application processes.
Each application process that defines a declared global temporary table
of the same name has its own unique description of the temporary table.
When the application process ends, the temporary table is dropped.
Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax
-----Original Message-----
From: fkany@xxxxxxxxxxxxxxxxxx [mailto:fkany@xxxxxxxxxxxxxxxxxx]
Sent: Monday, April 25, 2005 9:52 AM
To: CN=RPG programming on the AS400/O=iSeries
Subject: RE: Embedded SQL Problem
When in debug, the SQL statements would execute and create my global
temporary tables for the first report, but when the second report trys
to
use the BLD_SQL_STR subprocedure, it won't create the global temporary
tables.
Here is the complete subprocedure for the building and execution of the
SQL
statements:
========================================================================
===
* * BLD_SQL_STR - Build SQL Strings
*****************************************************************
P BLD_SQL_STR B
'* '* Report 1:
'* ---------
C IF REPORT# = '1'
'* C IF NUM#OT1 <> *ZEROS AND
C NUM#ODT <> *ZEROS
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and (FHOT = ' + %CHAR(NUM#OT1) +
' and FHDT = ' + %CHAR(NUM#ODT) +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
?* C ENDIF
'* C IF NUM#OT1 <> *ZEROS AND
C NUM#ODT = *ZEROS
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and (FHOT = ' + %CHAR(NUM#OT1) +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
?* C ENDIF
'* C IF NUM#OT1 = *ZEROS AND
C NUM#ODT <> *ZEROS
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and (FHDT = ' + %CHAR(NUM#ODT) +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with DATA';
/END-FREE
?* C ENDIF
?* C ENDIF
?* IF REPORT# = '1'
'* '* Report 2:
'* ---------
frankC IF REPORT# = '2'
'* C IF NUM#OT1 <> *ZEROS AND
C P1OREG <> '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and FHOT = ' + %CHAR(NUM#OT1) +
' and (CT1REG = ' + Q + %TRIM(P1OREG) + ' ' + Q +
' OR CT1REG = ' + Q + ' ' + %TRIM(P1OREG) + Q +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
'* C IF NUM#OT1 <> *ZEROS AND
C P1OREG = '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and FHOT = ' + %CHAR(NUM#OT1) +
') with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
'* C IF NUM#OT1 = *ZEROS AND
C P1OREG <> '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1REG AS REG1, CT1REG AS REG2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) + ') ' +
' and (CT1REG = ' + Q + %TRIM(P1OREG) + ' ' + Q +
' OR CT1REG = ' + Q + ' ' + %TRIM(P1OREG) + Q +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'REG1, REG2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
?* C ENDIF
?* IF REPORT# = '2'
'* '* Report 3:
'* ---------
frankC IF REPORT# = '3'
'* C IF NUM#OT1 <> *ZEROS AND
C P1OST <> '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1ST AS ST1, CT1ST AS ST2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and FHOT = ' + %CHAR(NUM#OT1) +
' and (CT1ST = ' + Q + %TRIM(P1OST) + ' ' + Q +
' OR CT1ST = ' + Q + ' ' + %TRIM(P1OST) + Q +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'ST1, ST2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
'* C IF NUM#OT1 <> *ZEROS AND
C P1OST = '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1ST AS ST1, CT1ST AS ST2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) +
') and FHOT = ' + %CHAR(NUM#OT1) +
') with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'ST1, ST2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
'* C IF NUM#OT1 = *ZEROS AND
C P1OST <> '*ALL'
/FREE
CLEAR SQLSTRING1;
SQLSTRING1 = 'Declare Global Temporary Table FR47P457C as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'CT1ST AS ST1, CT1ST AS ST2 ' +
'from FRL00144 left outer join AAP030 on ' +
'FHDT = CT1TID ' +
'where (FHDDAT >= ' + %CHAR(FDATE) +
' and FHDDAT <= ' + %CHAR(TDATE) + ') ' +
' and (CT1ST = ' + Q + %TRIM(P1OST) + ' ' + Q +
' OR CT1ST = ' + Q + ' ' + %TRIM(P1OST) + Q +
')) with data';
CLEAR SQLSTRING2;
SQLSTRING2 = 'Declare Global Temporary Table FR47P457B as ' +
'(Select ' +
'FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, ' +
'FHPUDT, FHSCD, FHCCD, FHCCT, FHCST, FHCZIP, ' +
'ST1, ST2, ' +
'FR47ONTIME(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as ONTIME, ' +
'FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE, ' +
'FR47LATE1(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE1, ' +
'FR47LATE2(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as LATE2, ' +
'FR47TRANS(FHOT, FHPRO, FHPUDT, FHDADT, ' +
'FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, ' +
'FHCZIP) as TRANS ' +
'from FR47P457C) with data';
/END-FREE
C ENDIF
?* C ENDIF
?* IF REPORT# = '3'
?* ?* Get All Records Needed for FR47P457C
C/EXEC SQL
C+ PREPARE SQLCMD FROM :SQLSTRING1
C/END-EXEC
C/EXEC SQL
C+ EXECUTE SQLCMD USING :FDATE, :TDATE
C/END-EXEC
?* ?* Get Service Quality for All Records in FR47P457C and
?* put in FR47P457B
C/EXEC SQL
C+ PREPARE SQLCMD FROM :SQLSTRING2
C/END-EXEC
C/EXEC SQL
C+ EXECUTE SQLCMD
C/END-EXEC
?* P BLD_SQL_STR E
========================================================================
===================
<SRamanujan@xxxxxxxxxxxxxxxxxx>@midrange.com on 04/25/2005 09:30:05 AM
Please respond to RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
To: <rpg400-l@xxxxxxxxxxxx>
cc:
Subject: RE: Embedded SQL Problem
Frank,
I would like to see if I could recreate the error at my end and help.
Please let me know the declare statement(s) and more details of the
error that has occurred.
Thanks
Sudha
Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax
-----Original Message-----
From: fkany@xxxxxxxxxxxxxxxxxx [mailto:fkany@xxxxxxxxxxxxxxxxxx]
Sent: Monday, April 25, 2005 8:55 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: Embedded SQL Problem
The program still won't execute report #2. When it trys to execute the
SQL
statements for report #2, the program bombs and gives me some kind of
SQL
dump that doesn't make any kind of sense. It's nothing like a program
dump.
Each subprocedure has multiple PREPARE & EXECUTE statements that run 1
after the other with no problems, but as soon as the program trys to
process report #2, the program crashes.
If anyone can help figure out what's going on, I'd appreciate it.
Here's
what's happening so far:
========================================================================
=========
Screen 1: User selects the reports they would like to print(Report 1,
Report 2, or Report 3)
<Enter Pressed>
(SQL RPG Program Begins)
If Report 1
callp PRCS_REPORT1
ENDIF
If Report 2
callp PRCS_REPORT2
ENDIF
If Report3
callp PRCS_REPORT3
ENDIF
start PRCS_REPORT1
SQLSTRING = 'DECLARE GLOBAL TEMPORARY TABLE.......'
/EXEC SQL
PREPARE SQLCMD FROM :SQLSTRING
EXECUTE SQLCMD
/END-EXEC
end PRCS_REPORT1
start PRCS_REPORT2
SQLSTRING = 'DECLARE GLOBAL TEMPORARY TABLE.......'
/EXEC SQL
PREPARE SQLCMD FROM :SQLSTRING
EXECUTE SQLCMD
/END-EXEC
end PRCS_REPORT2
start PRCS_REPORT3
SQLSTRING = 'DECLARE GLOBAL TEMPORARY TABLE.......'
/EXEC SQL
PREPARE SQLCMD FROM :SQLSTRING
EXECUTE SQLCMD
/END-EXEC
end PRCS_REPORT3
*INZSR BEGSR
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *iso,
C+ CloSqlCsr = *EndMod,
C+ Commit = *none
C/END-EXEC
ENDSR
(SQL RPG Program Ends)
========================================================================
=========
I can run Report 1, 2, or 3 one at a time with no problems, but if I
select
all 3 reports to print, the 1st report will print, but the program
crashes
on the 2nd report. Am I missing an SQL statement anywhere? I'm only
using
PREPARE and EXECUTE.
Thanks,
Frank
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
