AS400 ILE program with Embedded SQL

50 pts.
Tags:
ILE
SQL
I need to load a file based on several search criteria and then use the file to display in a subfile

Answer Wiki

Thanks. We'll let you know when a new response is added.

How much do you know about RPGIV and embedded SQL?

If you know enough…..

Write your RPG subfile program. Where you would normally set and read your database file to load the subfile, you put your SELECT embedded SQL statement (where the SETxx operation would go), and the FETCH statement in the loop where your READx operation would go.

The following code sample show how it would work to produce a printed report – it’s the same in principle for a subfile load.

Note the externally-defined data structure. This is required because the SELECT fields (also named in the FETCH) must exist in the program. Since the fields all come from the VDNDDOC file, it’s easiest just to create that externally-described structure.

Note that the variables containing the selection criteria are used in the SELECT statement preceded by a colon. This tells SQL that they are variables.

I hope this is helpful to you.

* ———————————————————————–
* Data record format data structure
* ———————————————————————–

D formatND E DS ExtName(VDNDDOC)

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

C *Entry Plist
C Parm p_KeyType
C Parm p_RcdKey1
C Parm p_RcdKey2

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*MainLine

* ————————————————–
* Incoming selection may be Shipper or Booking Party
* ————————————————–

C If KeyType = ‘Shipper’

C/EXEC SQL DECLARE C1 CURSOR FOR
C+ SELECT SUM(NDTCFT),
C+ SUM(NDTCF2), SUM(NDTCL2),
C+ SUM(NDTCF4), SUM(NDTCL4),
C+ NDSTPRCC, NDPOD
C+ FROM VDNDDOC
C+ WHERE NDSH = :p_RcdKey1
C+ GROUP BY NDSTPRCC, NDPOD
C+ ORDER BY NDSTPRCC, NDPOD
C/END-EXEC

C/EXEC SQL
C+ OPEN C1
C/END-EXEC

C Else

C/EXEC SQL DECLARE C2 CURSOR FOR
C+ SELECT SUM(NDTCFT),
C+ SUM(NDTCF2), SUM(NDTCL2),
C+ SUM(NDTCF4), SUM(NDTCL4),
C+ NDSTPRCC, NDPOD
C+ FROM VDNDDOC
C+ WHERE NDSTPRCC >= :p_RcdKey1 AND
C+ NDSTPRCC <= :p_RcdKey2 AND
C+ NDBP = ‘BG’
C+ GROUP BY NDSTPRCC, NDPOD
C+ ORDER BY NDSTPRCC, NDPOD
C/END-EXEC

C/EXEC SQL
C+ OPEN C2
C/END-EXEC

C EndIf

C/EXEC SQL WHENEVER NOT FOUND GO TO FINISHED
C/END-EXEC

C DoU SQLCOD = 100

C If p_KeyType = ‘Shipper’
C/EXEC SQL
C+ FETCH C1 INTO
C+ :NDTCFT, :NDTCF2, :NDTCL2, :NDTCF4, :NDTCL4,
C+ :NDSTPRCC, :NDPOD
C/END-EXEC
C Else
C/EXEC SQL
C+ FETCH C2 INTO
C+ :NDTCFT, :NDTCF2, :NDTCL2, :NDTCF4, :NDTCL4,
C+ :NDSTPRCC, :NDPOD
C/END-EXEC
C EndIf

C Except PrtDtl_1

C If P1CLIN > 59
C Add 1 RptPage
C Except PrtHdr_1
C Except PrtHdr_2
C Except PrtHdr_3
C Except PrtHdr_4
C Except PrtHdr_5
C EndIf

C EndDo

* End of data – print the totals and end the report :

C Finished Tag

C If p_KeyType = ‘Shipper’
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
C Else
C/EXEC SQL
C+ CLOSE C2
C/END-EXEC
C EndIf

C Except PrtTot_1
C Except PrtTot_3
C Except PrtFtr_1

C Close (E) QPRINT
C Eval *InLR = *On
C Return

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*EndMainLine

Regards,

SLoopy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Embeded
    I need to load a file based on several search criteria on 3 files and then use the file to display in a subfile. Even I have section my Fectch is not retriving that give SQLCOD = 100
    10 pointsBadges:
    report
  • TomLiotta
    If SQLCODE = 100, then no rows matched your search criteria. Either the WHERE clause is coded incorrectly for what you want to FETCH or there are no matching rows. Without seeing your code, that's as good as a guess can get. Please show us the relevant portions of code if more detail is needed. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following