本文介绍了DB2存储最终表子句的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

子句非常适合从DB2中的DML取回值,例如:

The FINAL TABLE clause is great for getting values back from DML in DB2, for example:

SELECT id
FROM FINAL TABLE
(
  INSERT INTO mySchema.myTable (val)
  VALUES ('data')
)

但是,似乎没有一种方法可以将查询的结果存储到另一个表中,从而将内容保留在某个地方。例如,以下两个操作均失败,并显示错误在指定的位置不允许使用数据更改表引用。 (我正在运行DB2 for i v7.1):

However, there doesn't seem to be a way to store the results of this query into another table, persisting the contents somewhere. For example, both of the following fail with the error "Data change table reference not allowed where specified." (I am running DB2 for i v7.1):

CREATE TABLE mySchema.otherTable AS (
SELECT id
FROM FINAL TABLE
(
  INSERT INTO mySchema.myTable (val)
  VALUES ('data')
)
) WITH DATA

在单独的CREATE TABLE语句中创建mySchema.otherTable之后,此操作也会失败:

After creating mySchema.otherTable in a separate CREATE TABLE statement, this also fails:

INSERT INTO mySchema.otherTable (ID)
SELECT id
FROM FINAL TABLE
(
  INSERT INTO mySchema.myTable (val)
  VALUES ('data')
)


推荐答案

不确定在i系列上是否可以使用,但是DB2 for LUW允许您执行以下操作:

Not sure if this works on i Series, but DB2 for LUW allows you to do this:

with i1 (id) as (
  SELECT id
  FROM FINAL TABLE
  (
    INSERT INTO mySchema.myTable (val)
    VALUES ('data')
  )
)
select * from new table (
  INSERT INTO mySchema.otherTable (ID)
  select id from i1
)

这篇关于DB2存储最终表子句的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 13:36
查看更多