RE: S/36-mode alt indexes

[bill.reger@xxxxxxxxxxxxx]

Dan,

You wrote:

>> Can I do a join thru SQL on s/36 flatfiles?  Anybody have an example?

Yes, you can SQL join S/36 flatfiles.  Assume we have two flatfiles CUST
and ORDER with RPG File and Input specs that look like the following:

FCUST    IPE F      20            DISK
ICUST    NS  01
I                                        1   5 CUST#
I                                        6  20 NAME

FORDER   IPE F      30            DISK
IORDER   NS  01
I                                        1   50ORDER#
I                                        6  10 CUST#
I                                       11  30 ORDDTL

The following SQL statement (I tested via *QMQRY) will join these files
based on matching CUST#'s:

SELECT
  ALL      SUBSTR(T01.CUST, 1, 5) AS "CUST#",
           SUBSTR(T01.CUST, 6, 15) AS "NAME",
           SUBSTR(T02.ORDER, 1, 5) AS "ORDER#",
           SUBSTR(T02.ORDER, 11, 20) AS "ORDDTL"
       FROM LIBRARY/CUST T01,
            LIBRARY/ORDER T02
WHERE     SUBSTR(T01.CUST, 1, 5) = SUBSTR(T02.ORDER, 6, 5)

Here is the sample output:

         CUST#  NAME             ORDER#  ORDDTL
         -----  ---------------  ------  --------------------
000001   A1234  JOHN Q. PUBLIC   00001   BICYCLE
000002   A1234  JOHN Q. PUBLIC   00002   RUNNING SHOES
******  * * * * *  E N D  O F  D A T A  * * * * *

Bill

--

NOTICE:  The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential.  If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected.