通用表表达式内部的窗口函数

Question

我试图在Db2上的公共表表达式中编写一个窗口函数，并收到一些意想不到的错误

这个查询在Db2 11.5.7

上运行

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select a.rowValue AS rowValue
from dummy as b
) b
WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 1;

但是这个查询:

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select max(a.rowValue) AS rowValue
from dummy as b
) b
WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

失败，出现错误

SQL0206N  "A.ROWVALUE" is not valid in the context where it is used.
SQLSTATE=42703

有趣的是，如果对a.rowValue执行一些标量操作，尽管我可以检索它。例如，下面的查询确实有效。

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select max(a.rowValue*b.phase) AS rowValue
from dummy as b
) b
WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

这个查询也可以。

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select max(a.rowValue*b.phase) AS rowValue
from dummy as b
group by a.phase
) b
WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

假设上面的查询工作，并产生预期的结果，我也希望这个查询，这是我真正想要的，工作。

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select max(a.rowValue*b.phase) over (partition by a.phase) AS rowValue
from dummy as b
) b
WHERE phase = 0
) 
SELECT * FROM solution WHERE phase = 0;

但是它失败了，并出现了这个错误。

SQL0206N  "ROWVALUE" is not valid in the context where it is used.
SQLSTATE=42703

这很有趣，因为我希望错误是这样的:

SQL0206N  "A.ROWVALUE" is not valid in the context where it is used.
SQLSTATE=42703

我真的很想在这里使用窗口函数，但不确定如何实现这些错误。我正在寻找a.解释为什么这些查询不起作用或b.替代语法，将达到相同的结果。

Answer 1

您应该打开IBM支持的案例。
可能的解决方法是使用一些内部表列注入一些表达式，这不会影响结果。

WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase from sysibm.sysdummy1
UNION ALL
SELECT 2 AS rowValue, 0 AS phase from sysibm.sysdummy1
),
solution(rowValue, phase) AS (
SELECT b.rowValue, phase FROM dummy a,
TABLE(
select 
max(a.rowValue + coalesce(b.rowValue, 0)*0) AS rowValue
from dummy as b
) b
WHERE phase = 0
) 
SELECT * 
FROM solution 
WHERE phase = 0

00