|
The declare global temporary table cannot be prompted. You just have to enter it in and it will run ... :( BTW, you can add an order by to the select statement and that's the sequence that the records are written to the temporary table. 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 1:56 PM To: CN=RPG programming on the AS400/O=iSeries Subject: RE: What would be your SQL solution? STRSQL won't let me use DECLARE. I did STRSQL and then entered DECLARE and tried to prompt it, but it wouldn't take. Any ideas? "Ross Hartford" <ross.hartford@xxxxxxxxxxx>@midrange.com on 04/07/2005 09: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) '* '* ---------- '* '* ---------------------- D FROMDATE S 8S 0 D TODATE S 8S 0 '* '* Arrays/Data Structures '* ---------------------- '* '* ----------- ?* Main Processing D MAIN_PRCS PR ********************************************************************** '* '* --------------------- C CALLP MAIN_PRCS '* '* ------------------- ?* End Program, Return to Caller C EVAL *INLR = *ON C RETURN * ?* C *ENTRY PLIST C PARM FROMDATE C PARM TODATE ?* * ?* -- 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 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.
midrange.com
