Multi-member file join

[d7d1cd_]

There is a multi-member file. When starting the program, it is indicated from which members the data should be taken. Moreover, this data must be connected with the data of another table.
I wanted to create a SQL view in order to make a join using a JOIN, but the problem is that the file is multi-member.
Tell me how to solve this problem?

[inigo.redin]

Use an ALIAS.

CREATE ALIAS
NAME_OF_ALIAS FOR NAME_OF_TABLE(NAME_OF_MEMBER);

[mathgregory]

hp deskjet 3755 wireless setup hp deskjet 3755 driver install hp deskjet 3755 printer setup hp officejet pro 8035 printer setup hp officejet pro 8035 driver install hp officejet pro 8035 wireless setup hp envy photo 7855 printer setup hp envy photo 7855 driver install hp envy photo 7855 wireless setup

[Vectorspace]

Links to fake sites, do not click.

[d7d1cd_]

Use an ALIAS.

CREATE ALIAS
NAME_OF_ALIAS FOR NAME_OF_TABLE(NAME_OF_MEMBER);

With all due respect, after creating an alias, it must be connected to another file, which is impossible. Let me explain. I wanted to connect to another file through creating a view. But when trying to create it I got an error that the view could not be created because the alias refers to a multi-member file.

[d7d1cd_]

Maybe the solution to my problem is to use the OVRDBF and OPNQRYF commands? OVRDBF to select the desired member, OPNQRYF to link the selected member to another table.

[JonBoy]

I don't think going backwards is a solution.

Why don't you just use the alias name as the table name? Works fine for me.

Have you not tried that?

[d7d1cd_]

I don't think going backwards is a solution.
Why don't you just use the alias name as the table name? Works fine for me.
Have you not tried that?

I tried. Here is my sequence of actions.

1. Created an alias for a specific member of a multi-member file:

create alias csntst/mbr3
for csntst/spac(mbr3)

2. Tried to create a view on the created alias:

create view csntst/v as (
  select *                
  from csntst/mbr3        
  where PAKEY = 'UWRKPROT'
)

The second point ends with an error:

Message ID . . . . . . :   SQL7030       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                          

Message . . . . :   Alias MBR3 for table SPAC in CSNTST not valid for          
  statement.                                                                  
Cause . . . . . :   The SQL statement cannot be performed on alias MBR3        
  because the alias refers to a member of table SPAC in schema CSNTST.        
Recovery  . . . :   Specify a valid table or an alias that does not refer to a
  member.  Try the request again.

I found information that this error is due to the fact that an alias is created for a specific member. If you create an alias without specifying a member (just to a file), then the view is created without problems. But I have a case when I need to work with a specific member.

[JonBoy]

As long as I live I will never understand the underlying rules for what SQL allows and does not allow. Sorry about that - it worked fine for a simple SELECT/JOIN scenario. I see Birgitta has offered a solution so hopefully that works for you.

[Birgitta Hauser]

AFAIK it is not possible to join an ALIAS of a multi-member file in an SQL View.
Is it necessary to have a view or can you do it in 2 steps in your program?
If you want to have a single object, you may intent to generate an UDTF (User Defined Table Function)?

This is for example my UDTF for reading Source Files:

https://gist.github.com/BirgittaHauser/8a95ec08f00f99f708ac42de0e5d9332

Birgitta

[d7d1cd_]

Is it necessary to have a view or can you do it in 2 steps in your program?

Birgitta, how to do it in 2 steps?

This is for example my UDTF for reading Source Files:
https://gist.github.com/BirgittaHaus...ac42de0e5d9332

I have very little experience with IBM i. Teach how to create a UDTF. I tried to execute the code from your example as a SQL statement, but I got an error.

[Birgitta Hauser]

You should explain, how (where, which tool) you tried to execute the SQL Statement and what exactly your error messages is.
Just in case there is a comma after the TIMEFMT row, you should remove it.

Birgitta

[d7d1cd_]

I launched a Database Development view from RDi, where I created an empty SQL script and inserted your function code there. I removed the comma after TIMEFMT, but got another error when trying to execute the script. The error appears in this part of the code:

Begin                                                                             
    Declare LocSQLCmd VarChar(1024) Default '';                                    
    Declare Continue Handler for SQLState '42704' Begin End;

Declare LocSQLCmd VarChar (1024) Default ''
[SQL0104] The lexem <END-OF-STATEMENT> is not valid. Allowed lexemes:;.