Re: Override PF in view

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