How can you use “create alias” to return all members of the primary file?

AFAIK, unless it’s a partitioned table, you can’t.

SQL doesn’t handle members. If you need SQL to process all rows in a table, the data shouldn’t be put into multiple members. But if you are already stuck with members. you can create a logical file that includes all members if it’s a keyed logical.

Since you probably have been stuck with multiple members, that might be your only choice for creating a permanent object. And since it can be done with a simple logical file, there doesn’t seem to be much reason to allow an ALIAS to do it.

If anybody knows an alternative, it’ll probably get posted here.

Tom

not elegant, but you could create a UNION statement over multiple ALIASed members

create alias s1…
create alias s2…
…
create alias sn…

select * from s1
union
select * from s2
union
…
select * from sn

If you need to automate the process you could use DSPFD MBRLIST to an outfile
Have an SQL-RPGLE read each member and
1. Issue an SQL to Create the Alias
2. Add to a string for the Create View as ‘ the additonal …
‘ union
select * from ‘ + aliasName
Once all the members have been processed run the string as a dynamic SQL .. once the view is created you can use it like a file
Phil
Phil