Db2 for i - 动态 FROM 子句

Question

环境：Db2 for i，版本 7.3

库/表结构：

CORPORATE/TENANTS
LIB01/INVOICE
LIB02/INVOICE
LIB03/INVOICE
…
LIBxx/INVOICE

CORPORATE/TENANTS表包含存储有关每个租户信息的库列表。它具有以下结构和数据：

CREATE OR REPLACE TABLE TENANTS (
ID     BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1),
TENANT CHAR(10) NOT NULL,
PRIMARY KEY(ID)
) RCDFMT TENANTSR;
RUNSQLSTM SRCFILE(HILLB/QDDLSRC) SRCMBR(TENANTS) DFTRDBCOL(CORPORATE)
+--+------+
|ID|TENANT|
+--+------+
| 1|LIB01 |
| 2|LIB02 |
|..|......|
|99|LIB99 |
+--+------+

LIBxx/INVOICE表彼此相同，并具有以下结构：

CREATE OR REPLACE TABLE INVOICE (
ID     BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1),
PAYDAT INTEGER(6,0) NOT NULL,
AMOUNT DECIMAL(15,2) NOT NULL DEFAULT 0,
PRIMARY KEY(ID)
) RCDFMT INVOICER;
+--+------+------+
|ID|PAYDAT|AMOUNT|
+--+------+------+
| 1|180701|100.00|
| 2|180801| 35.00|
|..|......|......|
+--+------+------+

我想为给定日期的所有租户生成发票金额列表：

180701 LIB01 100.00
180701 LIB02 140.00
180701 LIB03  74.00
…

从概念上讲，我想做的是这个(是的，我知道这是无效的SQL(：

SELECT PAYDAT, TENANT, AMOUNT
FROM $X.INVOICE
WHERE PAYDAT = 180701;

我想从INVOICE表中为每个TENANT提取数据，但我知道FROM子句不能像这样动态。我确定这种查询有一个名称，但我不知道它是什么，所以我无法有效地使用搜索引擎来查找我需要的东西。

用RPGLE程序解决这是微不足道的，但我需要一个纯粹的SQL解决方案。

请注意 - LIBxx 值不能以任何方式进行硬编码。这些值可以随时更改。

Answer 1

若要执行所需的操作，可以使用循环中带有EXECUTE IMMEDIATE的存储过程来生成结果集。像这样：

注意：这不是一个完整的剪切和粘贴解决方案，但您可以对其进行修改以执行所需的操作。

CREATE OR REPLACE PROCEDURE GETINVOICEAMOUNTS ( ) 
DYNAMIC RESULT SETS 1 
LANGUAGE SQL 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
CALLED ON NULL INPUT 
SET OPTION COMMIT = *NONE
BEGIN
DECLARE STMT VARCHAR(1024);
DECLARE RECORD_FOUND INTEGER DEFAULT 1;
DECLARE LIBRARY CHAR(10);
DECLARE C1 CURSOR FOR
SELECT TENANT FROM CORPORATE/TENANT;
DECLARE C2 CURSOR WITH RETURN FOR 
SELECT * FROM SESSION.TMP ; 
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET RECORD_FOUND = 0;
DECLARE GLOBAL TEMPORARY TABLE TMP 
(PAYDAT INTEGER(6,0),
TENANT CHAR(10),
AMOUNT DECIMAL(15,2))
WITH REPLACE;
OPEN C1;
LOOP
FETCH C1 INTO LIBRARY;
IF RECORD_FOUND = 0;
LEAVE LOOP;
END IF;        
SET STMT = 'INSERT INTO SESSION.TMP SELECT PAYDAT, LIBRARY, AMOUNT FROM ' || RTRIM(LIBRARY) || '.INVOICE WHERE PAYDAT = 180701';
EXECUTE IMMEDIATE STMT
END LOOP;
CLOSE C1;
OPEN C2; 
END; 

我给你的比我计划的要多。但是，您始终需要进行的一项特定修改是参数化要检索的日期。

这是它的工作原理： 名为TMP的全局临时表用于收集要在结果集中返回的记录。收集完所有记录后，将在TMP上打开一个游标，该过程结束。这会导致在 TMP 中收集的值作为结果集返回。

要收集值，将读取CORPORATE/TENANT文件，并将列TENANT检索到变量LIBRARY中。对于每条记录，将生成一个语句，该语句将LIBRARY连接到INSERT语句中。执行此语句，将记录加载到TMP中。我正在使用EXECUTE IMMEDIATE因为我无法使用参数标记来替换INSERT语句中的表引用，因此预准备语句只是额外的工作。

Answer 2

您可以使用UNION ALL：

SELECT sub.PAYDAT, t.TENANT, sub.AMOUNT
FROM (SELECT * FROM LIB01.INVOICE
UNION ALL
SELECT * FROM LIB02/INVOICE
...
SELECT * FROM LIB0n/INVOICE) sub
JOIN TENANTS t
ON sub.id = t.id
WHERE SUB.PAYDAT = 180701;

这是一个从销售中选择 * + @yymm模板。

---

编辑：

更安全的方法是创建一个视图：

CREATE VIEW combined_invoice
AS
SELECT * FROM LIB01.INVOICE
UNION ALL
SELECT * FROM LIB02/INVOICE
...
SELECT * FROM LIB0n/INVOICE;

和查询：

SELECT sub.PAYDAT, t.TENANT, sub.AMOUNT
FROM combined_invoice sub
JOIN TENANTS t
ON sub.id = t.id
WHERE SUB.PAYDAT = 180701;

当然，在添加/删除表后，视图应该改变。