SQL AS400多成员文件的SQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/346512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query of multi-member file on AS400
提问by tmtest
On AS400 in interactive SQL in a 5250 session,
在 5250 会话中的交互式 SQL 中的 AS400 上，
select * from myfile
returns rows from one member only when myfile has more than one member.
仅当 myfile 有多个成员时才返回来自一个成员的行。
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.
重要提示：最后我想用 jt400 通过 JDBC 来做这件事，所以我真的想要一个可以在那里工作的解决方案。
Thanks.
谢谢。
回答by Ryan Guill
You can create an alias using the create alias command:
您可以使用 create alias 命令创建别名：
CREATE ALIAS myLibrary/myAlias FOR memberLibrary/memberFile(memberName)
This will allow you to run sql against that member using the alias like you would any other file:
这将允许您像使用任何其他文件一样使用别名对该成员运行 sql：
SELECT * FROM myLibrary/myAlias
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:
请记住，别名会在您的会话结束后继续存在，它们不是临时的。因此，如果完成后不需要别名，请在 QTEMP 中创建别名或在完成后显式删除别名：
DROP ALIAS myLibrary/myAlias
HTH
HTH
回答by Kwebble
回答by naveen
SQL Alias
SQL 别名
OS/400 R430and later support an SQLalias 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 the CREATE ALIASdoes not have to exist when the ALIASis created. Any SQLtool, such as OS/400or i5/OS interactive SQL (STRSQL)or iSeries Navigator's Run SQL Scripts, can be used to create the alias, for example:
OS/400 R430后来支持SQL别名声明。为每个必须访问的成员创建一个别名，然后从应用程序中引用该别名。别名是一个持久对象——它只能被创建一次。创建CREATE ALIAS时， 中引用的成员不必存在ALIAS。任何SQL工具，例如OS/400ori5/OS interactive SQL (STRSQL)或iSeries Navigator's Run SQL Scripts，都可以用来创建别名，例如：
CREATE ALIAS MYLIB.FILE1MBR1 FOR MYLIB.MYFILE(MBR1)
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)
http://www-01.ibm.com/support/docview.wss?uid=nas1f1eaeecc0af19cc38625669100569213
http://www-01.ibm.com/support/docview.wss?uid=nas1f1eaeecc0af19cc38625669100569213
回答by JMerinoH
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:
有时您只需要创建别名作为一次性数据库分析，然后您想创建别名并在查询后立即将其删除；您还有一个包含许多表的数据库库，并且不想在每个查询中限定该库，因此您使用 SET SCHEMA；我喜欢使用 QTEMP 库来创建别名，因为 IBM i AS400 服务器上的 QTEMP 本质上是用于临时对象的：
set schema=mylibrary;
create alias qtemp.aliasx for table1(membera);
create alias qtemp.aliasy for table2(memberb);
select * from qtemp.aliasx;
select * from qtemp.aliasy;
drop alias qtemp.aliasx;
drop alias qtemp.aliasy;
