SQL View over a table with members
Home > IT Answers > DataManagement > SQL View over a table with members
Lpgast
0 pts.
0
Q:
SQL View over a table with members
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?
ASKED: Sep 30 2004  9:42 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
LFiato
0 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
simplest -- override to the member you want prior to doing the Sql statement
Last Answered: Sep 30 2004  12:11 AM GMT by LFiato   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Lpgast   0 pts.  |   Oct 1 2004  10:28AM GMT

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.

 

TomLiotta   7525 pts.  |   Oct 28 2009  1:15AM GMT

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

 

Droutt   50 pts.  |   Oct 28 2009  2:31PM GMT

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.

 

TomLiotta   7525 pts.  |   Oct 28 2009  10:04PM GMT

…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

 
0