× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. April 2005

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


--
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.





-- 
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 thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.