Re: Override PF in view

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Rob gave you an example of creating an alias and using it to create a 
view over it - an alias is actually a special kind of DDM file.

Mark's solution might be more germane - I assumed you wanted to override 
to a different member of the same file - so an alias can let you do that 
- so can the older technique of doing an override before using the file 
or view.

So Mark is suggesting you have 2 views and override the view with 
another view that specifies your other table.

In any case, the OVRDBF needs to be executed when you are using the 
view, not when you create the view. This is because all a view has is 
the SELECT statement, and that execution should honor the override - 
I've not tested this. And maybe this is what happened to you, eh?

Another approach is to have an LF over both TABLE1 and TABLE1_2016 - SQL 
doesn't know this is really 2 tables - it sees it as one table.

Then in your programs, you can test for a field that contains the year - 
or dates in a year - just a thought that might not apply to your 
situation. We do this with some files that have members for each of 
multiple years - same basic idea - so a multiple member file can be read 
through an LF over the members you specify.

HTH
Vern

On 1/27/2017 10:34 AM, Justin Taylor wrote:
> There may be 20 jobs concurrently accessing the view.  19 want may want MYLIB.TABLE1, while 1 job wants MYLIB.TABLE1_2016.  I guess I need to go back review alias, because I didn't think they could do that.
>
>
>
> -----Original Message-----
> From: Rob Berendt [mailto:rob@xxxxxxxxx]
> Sent: Friday, January 27, 2017 10:25 AM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Subject: RE: Override PF in view
>
> Looks doable
>
> CREATE TABLE QTEMP.X (MYCOL CHAR (5 ) NOT NULL WITH DEFAULT) Table X created in QTEMP.
> CREATE ALIAS QTEMP.XALIAS FOR QTEMP.X
> Alias XALIAS created in QTEMP.
> select * from qtemp.xalias
> SELECT statement run complete.
> create view qtemp.xview as (select * from qtemp.xalias) View XVIEW created in QTEMP.
> select * from qtemp.xview
> SELECT statement run complete.
> drop alias qtemp.xalias
> Drop of XALIAS in QTEMP complete.
> CREATE ALIAS QTEMP.XALIAS FOR QTEMP.X
> Alias XALIAS created in QTEMP.
> select * from qtemp.xview
> SELECT statement run complete.
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to:  2505 Dekko Drive
>            Garrett, IN 46738
> Ship to:  Dock 108
>            6928N 400E
>            Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:   01/27/2017 11:08 AM
> Subject:        RE: Override PF in view
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> Are you saying to replace the table in the view (e.g. MYLIB.TABLE1) with
> an alias?
>
>
> Sample:
> Create view JOIN_VIEW as (
>    Select * from MYLIB.TABLE1 A
>    Join select * from MYLIB.TABLE2 B on A.ID = B.ID
>    Join select * from MYLIB.TABLE3 C on B.ID = C.ID
> )
>
> Select *
>    From JOIN_VIEW
>
>
>
>
>
> -----Original Message-----
> From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
> Sent: Thursday, January 26, 2017 4:30 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Subject: Re: Override PF in view
>
> Hi Justin
>
> I'll take a WAG at this. You probably all this, but ...
>
> When you create a view, all you have is the SELECT statement encapsulated
> in what is basically a logical file - with no data, no entries.
>
> There is no connection to any kind of override when the view is used - the
> SELECT statement is run at that time.
>
> So maybe create an ALIAS and refer to that instead of the PF in the SELECT
> statement.
>
> Maybe???
>
> Vern