Re: SQL views over multi member physical file

On 8/8/2013 2:09 PM, Alan Shore wrote:

Hi everyone
Before I forget - we are on V5r4
We have a multi-member file (presently 16 members - but can increase)
There is a need is to create individual SQL views over each of these members
I understand/know about alias's, but from what I have googled/yahoo'ed/searched, it looks like I cannot create a view over an alias
Question 1 - is this in fact true?

Yes. SQL0730 says Cause . . . . . : The SQL statement cannot be
performed on alias ALIAS because the alias refers to a member of table
TABLE in schema SCHEMA.
Recovery . . . : Specify a valid table or an alias that does not
refer to a member. Try the request again.

Question 2 - In creating the alias (or view for that matter), is there a way to limit the alias (or view for that matter) to the LAST n records?

create view NEW_VIEW (id, name) as
(with count as
(select max(rrn(BASE_TABLE)) as max
from BASE_TABLE)
select ID, last concat first
from BASE_TABLE
where rrn(BASE_TABLE) >
(select max-10 from count))

I'm not at all sure this was helpful, because I don't think there's an
easy way to create a separate view over each individual member. SQL and
multi-member files are not the best of friends.

I hate to suggest this but you may need to trigger the multi0membered
file and update a parallel SQL table.

Why do you need individual SQL views? Won't a traditional LF work?
--buck