SQL View over a table with members

pts.
Tags:
IBM DB2
UDB for iSeries/i5
Hi all, I have an as/400 table(fileA) with several members, I need to create view that joins this to several other tables but but I need to only target one member in fileA. The other tables are all single member tables. I tried to create an alias over the table and then create the view but that is not allowed. I also tried to create a Logical file only over the specific member, but you can't use logical files in a view either. I know I could create a stored proc to do the join, but the vendor that installed our Reporting app(webfocus, which all this is for) has yet to give us details on how to setup WebFocus to use Iseries stored procs. Does anyone have a an idea how to create a view over members in a table?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use CREATE ALIAS . Suppose you have file with 3 members LIB/FIL(Mem1) , LIB/FIL(Mem2) LIB/FIL(MEM3). And you have to use MEM2.
Then use command
CREATE ALIAS LIB/MEM2 on LIB/FIL/MEM2.
sELECT * FROM LIB/MEM2

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Lpgast
    Yeah, that would work if I was able to run a program or stored proc before I generate my reports. But, I am using webfocus to do the reporting, which unfortunately I am unfamiliar with. The vendor that sold/installed/maintains the product for us is too stupid to mention. All of sudden they aren't AS/400 experts and don't know how to configure the product to be able to execute programs and/or stored procedures. Which is why I wanted to use a view so there isn't any pre-reporting operations that would have to be run.
    0 pointsBadges:
    report
  • TomLiotta
    Note that every view is a view over a member -- it's just that it's over the *FIRST member where *FIRST will be the member that comes first in a creation-date ordered list. Do you need a specific member? or do you need your view to be over one member that will be chosen at run-time? If you're lucky and it really is a "specific" member, You might be able to copy/delete/rename members in the sequence that causes your chosen member to get moved to the top of the list. If you're lucky. Tom
    125,585 pointsBadges:
    report
  • Droutt
    Using sql, create a view over the table and the member you need - assign it a unique name. Once that has been created and stored on your system, you can use the view name in your sql code like it was a file name.
    75 pointsBadges:
    report
  • TomLiotta
    ...create a view over the table and the member you need... How would you do that? I haven't dug into partitioning yet. Perhaps that provides a way. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following