Question

AS400 ILE program with Embedded SQL

SQL, ILE

I need to load a file based on several search criteria and then use the file to display in a subfile

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