|
midrange.com
I used OCCURS instead of DIM and I still received the same error. Here is
the changed code:
* SQL block FETCH array data structure
D FHSQLREC DS OCCURS(50)
D FHOT LIKE(AFHOT)
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)
Could I be using SQL the wrong way? Does anyone have a suggestion on a better
way to handle what I'm trying to do?
Before I tried using embedded SQL, I had the program in a READ loop. The file
it READs has millions of records in it. It took an hour or more to
read all the
records and spit out a report. I had heard that I should replace the READ with
SQL to increase performance.
Using the information below, would anyone have any ideas of how I can adjust my
SQL to do what I need it to do?
or
If , CREATE VIEW or CREATE TABLE would be a better method, please show me how
you would use the code below to do it.
Thanks,
Frank
"Russell Conerly" <rconerly@xxxxxxxxxxx>@midrange.com on 04/04/2005
04:13:09 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...
Shouldn't that have an occurs and not a DIM statement in the DS? You are
retrieving rows...I think that would be a multiple occurence DS.
IMHO,
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 FHOT LIKE(AFHOT)
> 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)
>
============================================================================
> * > * GET_TERM_BILLS - Get Freight Bills by Terminal
>
**********************************************************************
> P GET_TERM_BILLS B
> ?* > ?* Get Numeric Version of Origin Terminal
> 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
> ?* > ?* Get Numeric Version of Destination Terminal
> 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
> ?* > ?* Set Freight Cursor
> 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/END-EXEC
> '* > C/END-EXEC
> '* > '* Process SQL Rows
> '* ----------------
> C EVAL EOFFLG = 'N'
> C DOU EOFFLG = 'Y'
> ?* > ?* Fetch 50 Records
> ERRORC/EXEC SQL FETCH FREIGHT FOR 50 ROWS INTO :FHSQLREC
> C/END-EXEC
> ?* > ?* Process 50 Records
> C 1 DO 50 X
> ?* > ?* Validate Freight Bills
> C EVAL BADDATE = 'N'
> C EVAL NEXTFLG = 'N'
> C CALLP CHK_TERM_BILLS
> ?* > ?* Invalid Date Range
> C IF BADDATE = 'Y'
> C LEAVE
> C ENDIF
> ?* > ?* Invalid Freight Bill; Get Next Record
> C IF NEXTFLG = 'Y'
> C ITER
> C ENDIF
> ?* > ?* Check Service Quality
> C CALLP CHK_SVC_QLTY
> ?* > ?* Populate Work File(FR47P457) fields
> C CALLP POP_WORKF
> ?* > ?* Write Record to Work File(FR47P457)
> C WRITE FREC457
> ?* > C ENDDO
> ?* 1 DO 50 X
> ?* > C ENDDO
> ?* DOU %EOF(AAP030)
> ?* > ?* The dreaded TAG
> C TAG1 TAG
> ?* > P GET_TERM_BILLS E
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.
