RE: S/36-mode alt indexes

Looks good, Bill, and now I display my SQL-relative-newbie status...  How do
create an index I can use to CHAIN or SETLL/READE in an RPG program?

- Dan Bale
(I am *NOT* "Dale"
http://archive.midrange.com/midrange-l/200105/msg00281.html )
SAMSA, Inc.
989-790-0507
DBale@SAMSA.com <mailto:DBale@SAMSA.com>
  Quiquid latine dictum sit altum viditur.
  (Whatever is said in Latin seems profound.)

-----Original Message-----
From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
Behalf Of bill.reger@convergys.com
Sent: Tuesday, April 30, 2002 4:41 PM
To: rpg400-l@midrange.com
Subject: RE: S/36-mode alt indexes

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