RE: SQL views over multi member physical file

Thanks for the reply Sam - but a table wouldn't automatically refresh the f=data and with a multitude of users wanting to see this information, it would prove to be a !@#$% logistically

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 Sam_L
Sent: Thursday, August 08, 2013 4:57 PM
To: Midrange Systems Technical Discussion
Subject: 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


Disclaimer: This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.