Re: SQL views over multi member physical file

Even if you could handle members, how would you determine the last "n" rows? I think you will need a key, thought I suppose if your data is static and you don't reused deleted records you might get away without one.

Given that, one alternative might be to write a User Defined Table function with an RPG program behind it.

Select t.*
from mytable('MEMBER_1',500) as t

would return the last 500 rows of MEMBER_1.

For efficiency, the RPG could start at the end of the member and read backwards.

You would have to train your internet group in this syntax, but hey, aren't they SQL geeks?

I doubt if you could put the above in a view, given that you are in essence passing two parameters, but if you don't care about the last "n" efficiency, you could might be able to create multiple, parameter-less UDTFs.

Scott Klement has a useful article that got me started.

Enough rambling...

Sam

On 8/8/2013 4:33 PM, Alan Shore wrote:

Thanks for the reply Buck.
Still googling (sounds dirty) but rapidly giving up.
Your idea of a separate file is one that I had already thought of and now leaning more towards
The reason why views are required is for the internet group (forget what system etc. that they use) but the only way that they can look at the data on the AS/400 is by creating views over the AS/400 physical files. Unfortunately these multi member files are a pain the !@#$.

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck Calabro
Sent: Thursday, August 08, 2013 4:13 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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