× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. August 2013

RE: SQL views over multi member physical file

Actually about 25 to 30 years old

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 Gary Thompson
Sent: Thursday, August 08, 2013 4:39 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL views over multi member physical file

Let me guess, you are working with files designed about 40 years ago ?

We have a system like that and while members for source code makes sense to me, members for transaction data is a form of punishment.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, August 08, 2013 2:34 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL views over multi member physical file

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



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.