RE: UNION FILES -- RPG400-L

The code I posted earlier creates the following file. I had 2 records in
each based on PF. this logical file had 4 records in DBU.
I agree that there is no DDS keyword that does a union and obviously this
file I created is NOT going to work but what I am trying to do is;


We have 2 files that have 9 fields apiece that we want to use. These fields
have different names in each file. The fields have the same characteristics.
we want to have these fields in one file with one record format. I am trying
to determine if this can be created without using a work file and a program
to create it, since thi is going to be used by crystal reports.

TO give an example of why we need this. I have a telephone number, I look in
the main subscriber file and if its not there, I will look in the additional
numbers file. I was told by the cystal reports person  that he can't do
that. He needs the subscriber info in one file.

Thanks for your help.

  4/18/02  9:13:34               FILE DOCUMENTATION                     PAGE
1
 FILE DESCRIPTION..: Union Join of TIPSUBS/TIPADD# on sub#,NPA,NXX,LINE

 RECORD LENGTH.....:    31

 # OF FIELDS.......:     9

 FILE NAME   LIBRARY   TYPE                KEY FIELDS/SELECT OMITS

 ---------- ---------- ----
-----------------------------------------------------
 TILSUBADD# HDBACMS    *LGL

    Format: TIFADD#         ADSUB#    ADNPA     ADNXX     ADLINE

    Format: TIFSUBS         SBSUB#    SBNPA     SBNXX     SBLIN


----------------------------------------------------------------------------
----
 FIELD DESCRIPTION                      FIELD         TYPE    LEN   DEC
POS

----------------------------------------------------------------------------
----
 SUBSCRIBER NUMBER                      SBSUB#          P      9     0
1
 AREA CODE                              SBNPA           P      3     0
6
 NXX EXCHANGE                           SBNXX           P      3     0
8
 LINE                                   SBLIN           P      4     0
10
 DISCONNECT DATE                        SBDISD          P      8     0
13
 TERMINATION DATE                       SBTERM          P      8     0
18
 LISTING CODE  L=LISTED UP=UNPUBLISHED  SBLIST          A      2
23
 DISCONN FOR NON-PAYMENT DATE           SBPMTD          P      8     0
25
 MARKET TYPE                            SBMTYP          A      2
30
 SUBSCRIBER NUMBER                      ADSUB#          P      9     0
1
 AREA CODE                              ADNPA           P      3     0
6
 NXX EXCHANGE                           ADNXX           P      3     0
8
 LINE                                   ADLINE          P      4     0
10
 DISCONNECT DATE                        ADDISD          P      8     0
13
 TERMINATION DATE                       ADTERM          P      8     0
18
 LISTING                                ADLIST          A      2
23
 DISCONN FOR NON-PAYMENT DATE           ADPMTD          P      8     0
25
 MARKET TYPE                            ADMTYP          A      2
30
                              ** END OF DOCUMENT **


> -----Original Message-----
> From: David & Eileen Keck [SMTP:bstars@optonline.net]
> Sent: Thursday, April 18, 2002 9:04 AM
> To:   rpg400-l@midrange.com
> Subject:      Re: UNION FILES
>
> Dean,  Just a couple of notes ...
> 1. There is no UNION DDS keyword, UNION is an SQL verb used to merge two
> or
> more PF's into a single new PF.
> 2. You can use the JOIN DDS keyword to create an LF that merges the two or
> more PF's
> 3. PF's are not able to have more than one record format.
> 4. The code you provided creates two LF's ... not a single merged view of
> any kind.
> 5. The 4th field defined in your LF's has a different name in each file
> (typo) ?
> Could you clarify some what you are doing base on the above.  Thanks -
> Dave
> K.
>
> ----- Original Message -----
> > Good Morning All, I have to create a logical file (UNION over two PF's
> with
> > different record formats.) The PF's that this is to be built over have
> only
> > one record format apiece. My problem is that crystal reports is having a
> > hard time seeing this file and the file itself doesnt seem to have the
> total
> > records of BOTH based on PF's. I created the file as follows; Does
> anyone
> > have any insights on this matter. I know I cant create a Union LF that
> has
> > only one rec format, at least I think thats the case, LOL. Gads its
> > earlyyyy. Thanks all, code is below.
> >
> >   * SUBSCRIBER (MAIN) FILE
> >  A          R TIFSUBS                   PFILE(TIPSUBS)
> >  A            SBSUB#
> >  A            SBNPA
> >  A            SBNXX
> >  A            SBLIN
> >  A            SBDISD
> >  A            SBTERM
> >  A            SBLIST
> >  A            SBPMTD
> >  A            SBMTYP
> >   *
> >  A          K SBSUB#
> >  A          K SBNPA
> >  A          K SBNXX
> >  A          K SBLIN
> >   *
> >   * +-------------------------------------------------------------
> >   *
> >   * ADDITIONAL NUMBER FILE
> >   *
> >  A          R TIFADD#                   PFILE(TIPADD#)
> >  A            ADSUB#
> >  A            ADNPA
> >   A            ADNXX
> >   A            ADLINE
> >   A            ADDISD
> >   A            ADTERM
> >   A            ADLIST
> >   A            ADPMTD
> >   A            ADMTYP
> >    *
> >   A          K ADSUB#
> >   A          K ADNPA
> >   A          K ADNXX
> >   A          K ADLINE
> >    *
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.