首先在完全选择中获取 [英] fetch first in full select
问题描述
看来DB2对使用fetch first
N行有任意限制。
我想从一个大表查询中插入一个表，但只插入
前N行：
insert into target（选择colA，colB from source from where fetch
first 1000 rows）;
DB2对此有所了解。
有没有人有解决方法？
TIA，
RR
RR写道：
似乎DB2对使用fetch first
N行有任意限制。 。
当询问有关任何软件产品的问题时，预计会提供平台，
操作系统和产品版本。
I想要从一个大表查询中插入一个表，但只插入前N行：
插入到目标中（选择colA，colB，来自源，其中条件提取
仅限1000行;
DB2对此有所了解。
在询问有关任何软件产品的问题时，还应该引用任何
错误或警告消息。
有没有人有一个解决方法吗？
是的 - 我使用了DB2 UDB V8.2标识：
D：\\ \\ working> db2level
DB21085I Instance" DB2"使用32表示32。位和DB2代码版本SQL08020
级别标识符" 03010106"。
信息令牌是DB2 v8.1.7.445，s040812，WR21342和修订包 ; 7"。
产品安装在D：\ SQLLIB中。
我使用了SAMPLE数据库 - 表ORG。首先我创建了一个ORG副本：
D：\ Workinging> db2 create table org2 like org
DB20000I SQL命令成功完成。
然后我按照位置从ORG插入前3行：
D：\工作> db2插入org2（从组织顺序选择*）首先获取位置
仅限3行）
DB20000I SQL命令成功完成。
最后我显示了ORG2的内容：
D：\工作> db2 select * from org2
DEPTNUMB DEPTNAME经理分区
--- ----- -------------- ------- ---------- -------------
38 South Atlantic 30 Eastern Atlanta
15 New England 50 Eastern Boston
42 Great Lakes 100 Midwest Chicago
我没有看到任何问题 - 你呢？
Jan M. Nelken
RR写道：< blockquote clas s =post_quotes>
看来DB2对使用首先获取N行有任意限制。
我想要从大表查询插入到表中，但只插入前N行：
插入目标（从源中选择colA，colB，条件提取首先是1000）只有这些行;
DB2对此有所了解。
有没有人有解决方法呢？
假设你在DB2上对于LUW，是的：升级到至少V8.1 FP2
如果你不能使用ROW_NUMBER（）OVER（）OLAP表达式来
数字行并过滤掉除了第一个n之外的所有内容。
如果你不能这样做你将不得不用FFnR打开一个游标并且
INSERT the rows one一个人。或者您可以使用外部语言中的SCRATCHPAD写一个counter（）函数
来模仿ROW_NUMBER（）
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
" Jan M. Nelken" <的Un ********** @ Invalid.Domain>在留言中写道
news：vL ******************** @ rogers.com ...当询问有关任何软件产品的问题，预计会提供
平台，操作系统和产品版本。
感谢您的回复：
Hi,
It appears that DB2 has an arbitrary restriction on the use of "fetch first
N rows".
I want to insert into a table from a large table query, but only insert the
first N rows:
insert into target (select colA,colB from source where conditions fetch
first 1000 rows only);
DB2 compains about this.
Does anyone have a workaround for this?
TIA,
RR
RR wrote:Hi,
It appears that DB2 has an arbitrary restriction on the use of "fetch first
N rows".
When asking question about any software product, it is expected that platform,
OS and product version are provided.
I want to insert into a table from a large table query, but only insert the
first N rows:
insert into target (select colA,colB from source where conditions fetch
first 1000 rows only);
DB2 compains about this.
When asking question about any siftware product it is also expected that any
error or warning messages are quoted.
Does anyone have a workaround for this?
Yes - I used DB2 UDB V8.2 as identified by:
D:\Working>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with
level identifier "03010106".
Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and FixPak "7".
Product is installed at "D:\SQLLIB".
I used SAMPLE database - table ORG. First I created a copy of ORG:
D:\Working>db2 create table org2 like org
DB20000I The SQL command completed successfully.
Then I inserted first 3 rows from ORG ordered by location:
D:\Working>db2 insert into org2 (select * from org order by location fetch first
3 rows only)
DB20000I The SQL command completed successfully.
Finally I displayed contents of ORG2:
D:\Working>db2 select * from org2
DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION
-------- -------------- ------- ---------- -------------
38 South Atlantic 30 Eastern Atlanta
15 New England 50 Eastern Boston
42 Great Lakes 100 Midwest Chicago
I don''t see any problems - do you?
Jan M. Nelken
RR wrote:Hi,
It appears that DB2 has an arbitrary restriction on the use of "fetch first
N rows".
I want to insert into a table from a large table query, but only insert the
first N rows:
insert into target (select colA,colB from source where conditions fetch
first 1000 rows only);
DB2 compains about this.
Does anyone have a workaround for this?
Assuming you are on DB2 for LUW, yes: upgrade to at least V8.1 FP2
If you can''t you can use the ROW_NUMBER() OVER() OLAP expression to
number rows and filter out all but the first n.
If you can''t do that either You''ll have to open a cursor with FFnR and
INSERT the rows one by one. Or you could write a counter() function
using the SCRATCHPAD in an external language to mimic ROW_NUMBER()
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:vL********************@rogers.com...When asking question about any software product, it is expected that platform, OS and product version are provided.
Thanks for your reply:
这篇关于首先在完全选择中获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
