|
midrange.com
I need those fields in my " 1 DO 50 X " processing loop. I'm using
the SQL fetch to grab
50 records, process the records, write records to a file, then go get the
next 50 records. This loop was created for the SQL block fetch.
I'd rather not use a loop with in a loop if possible.
Is there a simpler method of getting all the data I need at 1 time and just
loop through those records, instead of getting just 50 at a time and
looping through
those 50, then grabbing another 50?
Thanks,
Frank
"Russell Conerly" <rconerly@xxxxxxxxxxx>@midrange.com on 04/04/2005
10:52:02 PM
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: SQLRPGLE won't compile...
Ok...The other thing I noticed is that you are selecting two fields in the
cursor Freight. Your DS has around 12 fields...eliminate the extra
fields.
Regarding performance...change your Where clause to: WHERE FHDDAT between
:FDATE AND :TDATE. This statement is a little bit easier to code and to
follow. I also believe it will help from a performance standpoint. This
statement is inclusive of the FROM and the TO date.
I also noticed you have DISTINCT coded. I'm assuming that you have
multiple FHOT, FHPRO and you are trying to pair these down. If not...nix
the distinct. Do you have an index already ordered by FHOT & FHPRO? The
query optimizer will probably require the addition of FHDDAT to the index
also.
Once you get the program to compile...place it in debug and place a
breakpoint after the OPEN statement....check the job log...it will tell
you which access path it used or it will tell you what index it needs to
perform efficiently.
We'll keep plugging until we get it.
Regards,
Russell Conerly
Tupelo, MS
>>
>> The program below(only relevant code included), won't compile. I get
> this
>> one error:
>> "Position 49 Host structure array FHSQLREC not defined or not usable."
>> I've marked the section of code that is
>> giving the message with "ERROR".
>>
>> Can anyone see what the problem is? Also, please let me know if I'm not
>> using the embedded SQL correctly.
>>
>> TIA,
>>
>> Frank
>>
>
======================================================================================
>
>> ?* SQL block FETCH array data structure
>> D FHSQLREC DS QUALIFIED
>> D FHSQLFLDS DIM(50) LIKE(DUMMY)
>> D FHOT LIKE(DUMMY.FHOT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS)
>> D FHPRO LIKE(DUMMY.FHPRO)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDT LIKE(DUMMY.FHDT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDDAT LIKE(DUMMY.FHDDAT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHPUDT LIKE(DUMMY.FHPUDT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDADT LIKE(DUMMY.FHDADT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHSCD LIKE(DUMMY.FHSCD)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCCD LIKE(DUMMY.FHCCD)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCCT LIKE(DUMMY.FHCCT)
> INZ(*BLANKS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCST LIKE(DUMMY.FHCST)
> INZ(*BLANKS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCZIP LIKE(DUMMY.FHCZIP)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDTIM LIKE(DUMMY.FHDTIM)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> '* >> D FHPRO LIKE(AFHPRO)
>> D FHDT LIKE(AFHDT)
>> D FHDDAT LIKE(AFHDDAT)
>> D FHPUDT LIKE(AFHPUDT)
>> D FHDADT LIKE(AFHDADT)
>> D FHSCD LIKE(AFHSCD)
>> D FHCCD LIKE(AFHCCD)
>> D FHCCT LIKE(AFHCCT)
>> D FHCST LIKE(AFHCST)
>> D FHCZIP LIKE(AFHCZIP)
>> D FHDTIM LIKE(AFHDTIM)
>>
>
============================================================================
>
>> * >>
> **********************************************************************
>> P GET_TERM_BILLS B
>> ?* >> C IF #OT1 <> '*ALL'
>> C EVAL NUM#OT1 = *ZEROS
>> C #OT1 CHAIN AAL03002
>> C IF %FOUND(AAL03002)
>> C EVAL NUM#OT1 = CT1TID
>> C ENDIF
>> C ENDIF
>> ?* >> C IF #DT1 <> '*ALL'
>> C EVAL NUM#ODT = *ZEROS
>> C #DT1 CHAIN AAL03002
>> C IF %FOUND(AAL03002)
>> C EVAL NUM#ODT = CT1TID
>> C ENDIF
>> C ENDIF
>> ?* >> C/EXEC SQL
>> C+ DECLARE FREIGHT CURSOR FOR SELECT DISTINCT FHOT, FHPRO FROM
>> C+ FRL00144 WHERE FHDDAT >= :FDATE AND FHDDAT <= :TDATE ORDER BY
>> C+ FHOT, FHPRO
>> C/END-EXEC
>> '* >> '* >> '* >> '* ----------------
>> C EVAL EOFFLG = 'N'
>> C DOU EOFFLG = 'Y'
>> ?* >> ERRORC/EXEC SQL FETCH FREIGHT FOR 50 ROWS INTO :FHSQLREC
>> C/END-EXEC
>> ?* >> C 1 DO 50 X
>> ?* >> C EVAL BADDATE = 'N'
>> C EVAL NEXTFLG = 'N'
>> C CALLP CHK_TERM_BILLS
>> ?* >> C IF BADDATE = 'Y'
>> C LEAVE
>> C ENDIF
>> ?* >> C IF NEXTFLG = 'Y'
>> C ITER
>> C ENDIF
>> ?* >> C CALLP CHK_SVC_QLTY
>> ?* >> C CALLP POP_WORKF
>> ?* >> C WRITE FREC457
>> ?* >> ?* 1 DO 50 X
>> ?* >> ?* DOU %EOF(AAP030)
>> ?* >> C TAG1 TAG
>> ?*
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
