我尝试在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.一个替代语法，将达到相同的结果。