|
midrange.com
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'
'* '* OT to DT
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
'* '* OT to *ALL DT's
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
'* '* *ALL OT's to DT
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'
'* '* OT to Region
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
'* '* OT to *ALL regions
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
'* '* *ALL service centers to Region
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'
'* '* OT to State
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
'* '* OT to *ALL states
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
'* '* *ALL service centers to State
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
--
This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.
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.
