Can SQL be used to Create a View over a multi-member file - IBM AS400

This is a discussion on Can SQL be used to Create a View over a multi-member file - IBM AS400 ; I have a Sales file with 2 members (Ordered & Sold) This works: CREATE ALIAS Sold FOR Sales(Sold) SELECT * FROM Sold This gives an error: CREATE ALIAS Sold FOR Sales(Sold) CREATE VIEW SoldView AS SELECT * FROM Sold Error ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: Can SQL be used to Create a View over a multi-member file

  1. Can SQL be used to Create a View over a multi-member file

    I have a Sales file with 2 members (Ordered & Sold)

    This works:

    CREATE ALIAS Sold FOR Sales(Sold)
    SELECT * FROM Sold

    This gives an error:

    CREATE ALIAS Sold FOR Sales(Sold)
    CREATE VIEW SoldView AS SELECT * FROM Sold

    Error message:
    Message . . . . : Alias SOLDVIEW for table SALES not valid for
    statement.
    Recovery . . . : Specify a valid table or an alias that does not
    refer to a member.

    The manual sayes you can create a view over a table or alias. But
    apparently the alias can not reference a member when creating a view.

    Has anyone created a view over a multi-member file?

  2. Re: Can SQL be used to Create a View over a multi-member file

    eb wrote:
    > I have a Sales file with 2 members (Ordered & Sold)
    >
    > This works:
    >
    > CREATE ALIAS Sold FOR Sales(Sold)
    > SELECT * FROM Sold
    >
    > This gives an error:
    >
    > CREATE ALIAS Sold FOR Sales(Sold)
    > CREATE VIEW SoldView AS SELECT * FROM Sold
    >
    > Error message:
    > Message: Alias SOLDVIEW for table SALES not valid for
    > statement.
    > Recovery: Specify a valid table or an alias that does not
    > refer to a member.
    >
    > The manual says you can create a view over a table or alias. But
    > apparently the alias can not reference a member when creating a view.
    >
    > Has anyone created a view over a multi-member file?


    Odd, I thought an ALIAS was not even allowed in a VIEW.

    Regardless, a VIEW is limited always to data from the first member.

    To access data from another member, perform the SQL inside of a SQL
    program or SQL processor instead of a VIEW. And in those cases, I would
    recommend to use OVRDBF instead of an ALIAS, except when the ALIAS is
    intended to remain indefinitely.

    Regards, Chuck

  3. Re: Can SQL be used to Create a View over a multi-member file

    On Jul 9, 12:27*pm, CRPence wrote:
    > eb wrote:
    > > I have a Sales file with 2 members (Ordered & Sold)

    >
    > > This works:

    >
    > > CREATE ALIAS Sold FOR Sales(Sold)
    > > SELECT * FROM Sold

    >
    > > This gives an error:

    >
    > > CREATE ALIAS Sold FOR Sales(Sold)
    > > CREATE VIEW SoldView AS SELECT * FROM Sold

    >
    > > Error message:
    > > Message: * Alias SOLDVIEW for table SALES not valid for
    > > statement.
    > > Recovery: * Specify a valid table or an alias that does not
    > > refer to a member.

    >
    > > The manual says you can create a view over a table or alias. But
    > > apparently the alias can not reference a member when creating a view.

    >
    > > Has anyone created a view over a multi-member file?

    >
    > * *Odd, I thought an ALIAS was not even allowed in a VIEW.
    >
    > * *Regardless, a VIEW is limited always to data from the first member..
    >
    > * *To access data from another member, perform the SQL inside of a SQL
    > program or SQL processor instead of a VIEW. *And in those cases, I would
    > recommend to use OVRDBF instead of an ALIAS, except when the ALIAS is
    > intended to remain indefinitely.
    >
    > Regards, Chuck- Hide quoted text -
    >
    > - Show quoted text -


    I have been doing the OVRDBF for several months but I was trying to
    make it permanent. Instead of duplicating the SQL statement in
    multiple programs. The real SQL statement joins 4 physical files
    together, with the primary physical (Sales) having 2 members.

    I could create a LF Join, but one of the join fields in defined 2
    zoned in two of the files, and 3 zoned in the other two files. So a LF
    doesn't compile, but an SQL join handles it fine.

    I'll redefine the problem field to 3 zoned in all the files and create
    a LF Join.


+ Reply to Thread