On AS400 in interactive SQL in a 5250 session,
select * from myfile
returns rows from one member only when myfile has more than one member.
How can I get rows from a specific member?
Important: in the end I'd like to do this over JDBC with jt400 so really I want a solution that'll work there.
Thanks.
SQL Alias
OS/400 R430
and later support anSQL
alias statement. Create an alias for each member that must be accessed, then reference the alias from the application. The alias is a persistent object -- it must be created only once. The member referenced in theCREATE ALIAS
does not have to exist when theALIAS
is created. AnySQL
tool, such asOS/400
ori5/OS interactive SQL (STRSQL)
oriSeries Navigator's Run SQL Scripts
, can be used to create the alias, for example:http://www-01.ibm.com/support/docview.wss?uid=nas1f1eaeecc0af19cc38625669100569213
This is an old thread and still the first on the search results list, I would like to enhance the previous responses:
Sometimes you only need to create the alias as a one time Database analysis, then you want to create the alias and drop it off immediately after your query; you also have a Data Base library with many tables and don't want to qualify the library in every query so you use SET SCHEMA; I love to use the QTEMP library to create alias since QTEMP by nature on IBM i AS400 servers is for temporary objects:
You can create an alias using the create alias command:
This will allow you to run sql against that member using the alias like you would any other file:
Just remember that the alias will stick around after your session, they are not temporary. So if you wont need the alias when you are done, either create the alias in QTEMP or explicitly drop the alias once you are done with it:
HTH
Create an SQL alias for the member and query the alias, see this page for an example.