RE: LF on 2 files with 'UNION'

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
I believe you're right on the prefix.  Instead try the following:

CREATE VIEW rob.sudviewa
  (ccolumn1, ccolumn2)
  AS
  SELECT acolumn1, acolumn2 FROM rob.sud1
  UNION
  SELECT bcolumn1, bcolumn2 FROM rob.sud2;

Notice the (   ) around the new field names?

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




<SRamanujan@sungardfutures.com>
Sent by: midrange-l-admin@midrange.com
11/06/2002 03:36 PM
Please respond to midrange-l

        To:     <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: LF on 2 files with 'UNION'


Thanks Rob.  The addlfm gives me an error - > the Fileb not valid.  We are
on v5r1.  I tried the create view once again and got the same error :)
Perhaps the field name prefix being different, makes all the difference!
Sudha

-----Original Message-----
From: rob@dekko.com [mailto:rob@dekko.com]
Sent: Wednesday, November 06, 2002 2:11 PM
To: midrange-l@midrange.com
Subject: RE: LF on 2 files with 'UNION'


This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Ram,

Why would my Create View work and yours not?  I am at V5R2 - in case that
matters.

DDS is a dark confusing art.  But what happens if you keep your DDS
simple, like:
                 R AREC          PFILE(FILEA)
                   AFIELD1 R
                   AFIELD2 R
               AFIELD3 R

Compile it without any members:
CRTLF ... MBR(*NONE)
and then do:
ADDLFM FILE(MYVIEW) MBR(MYMBR) DTAMBRS((FILEA (FILEA)) (FILEB (*FILEB)))


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




<SRamanujan@sungardfutures.com>
Sent by: midrange-l-admin@midrange.com
11/06/2002 02:34 PM
Please respond to midrange-l

        To:     <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: LF on 2 files with 'UNION'


The 2 files have the same format name and are created from the same dds
but the field name prefix are different.

In the program I have it as
  select afield1,afield2,afield3 from filea where afield1 <> ' ' ...
   union select bfield1,bfield2,bfield3 from fileb where bfield1<> ' ' ...

which works fine.

I did something like the following that errored out.

 create view filevew  as
  select afield1,afield2,afield3 from filea
   union select bfileld1,bfield2,bfield3 from fileb

 UNION and UNION ALL for CREATE VIEW not valid.


I then created lf with 2 record formats with same selected fields as above
:

                 R AREC          PFILE(FILEA)
                   AFIELD1 R
                   AFIELD2 R
        AFIELD3 R
                 K AFIELD1
                 K AFIELD2
      K AFIELD3

                 R BREC          PFILE(FILEB)
                   AFIELD1 R
                   AFIELD2 R
        AFIELD3 R

                 K BFIELD1
                 K BFIELD2
      K BFIELD3

But when I do a select I get :

select * from LFFILE1
File LFFILE1 in SUDLIB has more than one format.

Thanks,
Sudha
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.