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



I was wondering why you need this in a view. The override scensarios would
work with dynamic SQL where the statement is resolved at runtime.

Have you looked at User Defined Table Functions (UDTF)? I havn't tried it, but
perhaps you could define the function to first call a stored procedure to issue
the override, then select the data to be returned from the function. In usage,
your syntax would look something like this...

Create Function mylib/OPAXS( in_lib varchar 10,
in_file varchar 10,
in_member varchar 10)
returns Table ....
...
Begin
Call mylib/SP_OvrDbf(mylib, myfile, mymbr);
RETURN Select * from in_file
End



in embedded SQL,

Select * from TABLE(mylib/OPAXS(:alib, :afile, :amember)


I don't know if UDTFs are subject to the same issues as View, but its worth a
look, maybe....

Eric


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Shore
Sent: Friday, June 29, 2007 9:21 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: creating a view


Thanks for your reply Eric, but sad to say when I run
SELECT * FROM INPOPAXS
after the OVRDBF, i get the raw data from the file (RMSFILES#/INPOP300)
MBR(M026) and not from the view.



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/29/2007 10:03:30 AM:

Lets try:

CREATE VIEW AXSLIB/INPOPAXS (HOUSE, PRDNO, DIFF) AS SELECT HOUSE,
PRDNO, SUM(QUANO - QUANR) FROM RMSFILES#/INPOP300 GROUP BY HOUSE,
PRDNO

OVRDBF FILE(INPOP300) TOFILE(RMSFILES#/INPOP300) MBR(M026)
(The override affects the open of file INPOP300, which is the file
that gets opened when the view is activated...)

and run
SELECT * FROM INPOPAXS

Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Shore
Sent: Friday, June 29, 2007 7:23 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: creating a view


Thanks Peter
Dope slaps on me
My create view was
CREATE VIEW AXSLIB/INPOPAXS (HOUSE, PRDNO, DIFF) AS SELECT HOUSE,
PRDNO, SUM(QUANO - QUANR) FROM RMSFILES#/INPOP300 GROUP BY HOUSE,
PRDNO

However when I change my OVRDBF to
OVRDBF FILE(INPOPAXS) TOFILE(RMSFILES#/INPOP300) MBR(M026)
(The correct way)
and run
SELECT * FROM INPOPAXS
I still get the data from the *FIRST member



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill



rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/28/2007 05:28:11 PM:

Isn't that TOFILE parm supposed to be TOFILE(RMSFILES#/INPOP300)?

What does your CREATE VIEW statement look like?

Alan Shore wrote:
Well Charles - I was right, I had done something wrong
I revisited your example and realized the original OVRDBF was in fact
WRONG
so I submitted
OVRDBF FILE(INPOPAXS) TOFILE(INPOP300/RMSFILES#)
MBR(M026)

where INPOPAXS is the view that I created
I then tried DBU and received a divide by 0 error
Within STRSQL I tried
select * from inpopaxs
and received the following error

INPOPAXS in INPOP300 type *FILE not found

Anyone any thoughts?


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-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.