Embedded Sql Select (w/Join) into RPG Externally Described Data Structure

*=====================================================================
Embedded Sql Select (w/Join) into RPG Externally Described Data Structure
  (...without having to manually describe much at all!)
*=====================================================================
MY ?QUESTION? IS at the bottom of this post?:

I had a hill-uva time finding an 'exact working example' of this so
 when I got everything running, I thought I'd post it for anyone
 else who might ever be looking for it....:

Use embedded SQL to join some files, and have the fields automatically
 put into external described RPGLE data structures based on the joined
 files' structure so you don't have to key in field names in
 data structures.  (no 'double-describing').

One gotcha I ran into was letting SQL use the *JOB date format so
 I had to specify DATFMT(*ISO) on the compile.

I found it helpful to throw in print output (QPRINT!) to
 check my results.  I included a column with sqlcod and sqlstate
 which I found extremely helpful.


      ****************************************************************
      * Embedded Sql Select (w/Join) into RPG Ext Described Data Structure
      ****************************************************************
      *  Printer file...
     FQPRINT    O    F  192        PRINTER OFLIND(*INOF)
      *****************************************************************
     D SQLCODO         S              6  0 INZ
     D NUMRECS         S              9  0 INZ(0)
      *
     D DS_F1         E DS                  EXTNAME(F1)
     D DS_F2         E DS                  EXTNAME(F2)
      *
      ****************************************************************
     C                   EXSR      $ONE
     C                   EVAL      *INLR = *ON
      ****************************************************************
     C     $ONE          BEGSR
      ****************************************************************
      * Main Line Calculations....
      *
      *
      * Declare Select Statement...
      *
     C/EXEC SQL
     C+    DECLARE C1 CURSOR FOR
     C+    SELECT *
     C+      FROM F1
     C+      LEFT OUTER JOIN F2
     C+      ON  F1.F1ON = F2.F2ON
     C+      AND F1.F1SHP# = F2.F2SHP#
     C/END-EXEC
      *
      * Open Cursor...
      *
     C/EXEC SQL
     C+    OPEN C1
     C/END-EXEC
      *
      *
      * Read Thru Until End Of File...
      *
      *                  ------
     C                   DOU       (SQLCOD = 100)
      *
     C/EXEC SQL
     C+    FETCH C1 INTO :DS_F1, :DS_F2
     C/END-EXEC
      *
     C                   EVAL      SQLCODO  = SQLCOD
      *
     C                   IF        (SQLCOD <> 100)
      *
     C                   EVAL      NUMRECS  = NUMRECS  + 1
     C                   EXCEPT    DETAIL
      *
     C                   ENDIF
      *
      *
     C                   ENDDO
      *                  ------
      *
      * Close the Cursor...
      *
     C/EXEC SQL
     C+    CLOSE C1
     C/END-EXEC
      *
      *
      *  Implied Last Record stuff...
     C                   EXCEPT    TOTAL
      *
     C                   ENDSR
      *
      * Heading lines for detail columns...
      *
     OQPRINT    H    1P                     1
     O         OR    OF
     O                                            7 'Order#'
     O                                           16 'Cust#'
     O                                           48 'cod'
     O                                           55 'STT'
     O                                           80 'Count'
      *
      * Detail output...
      *
     O          E            DETAIL         1
     O                       F1ON                 7
     O                       F2CUST              16
     O                       SQLCODO       J     48
     O                       SQLSTT              55
      *
     O                       NumRecs       Z     80
      *
      * End of Report Total  Calculations...
      *
     O          E            TOTAL       1  1
     O                                           15 'Num Recs --->'
     O                       NumRecs       Z     55


The code above has been edited to look clean and simple!
 (and therefore probably wouldn't function even if 'I'
   pasted it into a member! <g> )

I'm enjoying this sql stuff!

OPNQRYF begone!!

The power of SQL compels you!
The power of SQL compels you!
The power of SQL compels you!

What movie ?


John B.