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.
midrange.com
