RE: What would be your SQL solution? -- RPG400-L


You're right.  It doesn't like host variables in the RPGIV program for this
DECLARE statement.  How can I code it to except my host variable?  Here's
my current code:

C/EXEC SQL
C+ DECLARE GLOBAL TEMPORARY TABLE FR47P457A AS (SELECT FHOT, FHPRO,
C+ FHDT, FHDDAT, FHDTIM, FHDADT, FHPUDT, FHSCD, FHCCD, FHCCT, FHCZIP
C+ FROM FRL00144 WHERE FHDDAT >= :FDATE AND FHDDAT <= :TDATE) WITH
C+ DATA
C/END-EXEC

with the colons in front of FDATE and TDATE, the program gives a syntax error 
message.

Thanks,

Frank





"Ross Hartford" <ross.hartford@xxxxxxxxxxx>@midrange.com on 04/07/2005
10:46:21 AM

Please respond to RPG programming on the AS400 / iSeries
       <rpg400-l@xxxxxxxxxxxx>

Sent by:    rpg400-l-bounces@xxxxxxxxxxxx


To:    "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc:

Subject:    RE: What would be your SQL solution?


Mine takes one SQL statement and creates a temporary table in QTEMP with
the
selected records:

declare global temporary table temp1
as (
select x1,x2,n1,n2 from ross/xxxx where n1 > 0 and n2 > 0)

with data

I think you may have to build this as a dynamic SQL statement and execute
it, versus doing the where clause with host variables.  Just replace the
x1,x2, etc with your list of fields and give it a shot in STRSQL or your
favorite SQL test tool.

Ross Hartford
Cothern Computer Systems, Inc.
1640 Lelia Drive, Suite 200
Jackson, MS  39216

Phone:     601-718-0803
Fax:       601-969-1184
Support:   888-222-5577

email:     rossh@xxxxxxxxxxx
website:   www.ccslink.com

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of fkany@xxxxxxxxxxxxxxxxxx
Sent: Thursday, April 07, 2005 9:16 AM
To: CN=RPG programming on the AS400/O=iSeries
Subject: What would be your SQL solution?


I've been trying to figure out an embedded SQL solution for my RPGIV READ
loop for over a week now.  I feel like I'm "spinning my wheels".  I'm going
to post the details of my project.  If anyone would like to share how they
would use SQL to complete the task, please do so.

The program currently takes an hour or more to process.  I don't want to
create anymore logicals over the file it currently uses because the file
just seems to have too many logicals(close to 100).  Even with all the
logicals the file currently has, our users are still accustomed to waiting
a day or more for a large report to print.  I'd like to help speed things
up.  Nobody in our shop, including me, is aware of how fast SQL really is
at getting large amounts of data at 1 time.

Thanks,

Frank

System Information:           V5R2
Programming language:   RPGIV

(Part 1) - This part takes an hour or more to process using an RPGIV READ
loop with a typical date range of 1 month(20040501 to 20040531)
Program(RPGIVPGM1) is called with parameters(FROMDATE & TODATE):
CALL RPGIVPGM1(FROMDATE TODATE)

RPGIVPGM1 will populate BFILE with all records from AFILE within the date
range specified(FROMDATE & TODATE).  The only fields needed from AFILE to
populate BFILE are: FHOT, FHPRO,  FHDT, FHDDAT, FHDTIM, FHDADT, FHPUDT,
FHSCD, FHCCD, FHCCT, & FHCZIP.

BFILE is keyed only on FHDDAT, which is what date the FROMDATE & TODATE
should be referencing.

(Part 2) - After BFILE is built from Part 1, this section only takes a few
seconds to run.
RPGIVPGM2 will access BFILE by FHDDAT and print a report.

AFILE has 7 million records.  BFILE, if populated with 1 month of
data(20040501 to 20040531) will have 400,000 records.

RPGIV shell, please make any necessary changes:
     H DFTACTGRP(*NO)
     '*'* Files Used
     '* ----------
     '*'* Program Defined Fields
     '* ----------------------
     D FROMDATE        S              8S 0
     D TODATE          S              8S 0
     '*'* ----------------------
     '* Arrays/Data Structures
     '* ----------------------
     '*'* Prototypes:
     '* -----------
     ?* Main Processing
     D MAIN_PRCS       PR
     **********************************************************************

     '*'* Start Main Processing
     '* ---------------------
     C                   CALLP     MAIN_PRCS
     '*'* End Main Processing
     '* -------------------
     ?* End Program, Return to Caller
     C                   EVAL      *INLR = *ON
     C                   RETURN
     ***C     *INZSR        BEGSR
     ?*?* Incoming Parameter List
     C     *ENTRY        PLIST
     C                   PARM                    FROMDATE
     C                   PARM                    TODATE
     ?*C                   ENDSR
     ***P MAIN_PRCS       B
     ?*'*



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