DB2函数中使用WITH子句缓存查询结果长度失败求助

你尝试在CTE（公共表表达式）内部用INTO给变量leng赋值的写法不符合DB2语法——DB2不允许在CTE内部使用INTO子句给变量赋值，INTO仅能用于顶层SELECT语句，用来将单行查询结果赋值给变量。

以下是两种可行的解决方案：

方案1：先单独计算行数赋值，再处理JSON聚合 #

这种方式避免重复定义数据集逻辑，通过子查询直接计算总行数：

DECLARE result CLOB;
DECLARE leng INT;

-- 第一步：计算目标数据集的行数，赋值给leng
SELECT COUNT(*) INTO leng
FROM (
    SELECT id, item, itemScore, stage, product, url, score
    FROM PROD_T
) AS temp_base;

-- 第二步：生成聚合JSON数据
WITH BASE AS ( 
    SELECT id, item, 
      JSON_OBJECT(
          'item' VALUE item, 
          'itemScore' VALUE itemScore,
          'stage' VALUE stage,
          'reco' VALUE JSON_OBJECT(
              'product' VALUE product,
              'url' VALUE url ,
              'score' VALUE score 
              FORMAT JSON
          )
          FORMAT JSON ABSENT ON NULL  
          RETURNING VARCHAR(200) FORMAT JSON
      ) ITEM_JSON  
    FROM PROD_T  
),  
PROD_OBJS AS ( 
    SELECT  JSON_OBJECT ( 
        KEY 'id' VALUE ID , 
        KEY 'itens' VALUE 
               JSON_ARRAY ( LISTAGG( ITEM_JSON , ', ') WITHIN GROUP (ORDER BY ITEM) FORMAT JSON )
        FORMAT JSON 
    ) json_objects  
    FROM BASE GROUP BY ID 
) 
SELECT JSON_ARRAY (SELECT json_objects FROM PROD_OBJS FORMAT JSON) INTO result 
FROM SYSIBM.SYSDUMMY1;

方案2：先定义BASE CTE，再单独查询行数赋值 #

如果希望复用BASE的定义，可以先声明CTE，再单独查询行数赋值：

DECLARE result CLOB;
DECLARE leng INT;

-- 定义BASE数据集
WITH BASE AS ( 
    SELECT id, item, 
      JSON_OBJECT(
          'item' VALUE item, 
          'itemScore' VALUE itemScore,
          'stage' VALUE stage,
          'reco' VALUE JSON_OBJECT(
              'product' VALUE product,
              'url' VALUE url ,
              'score' VALUE score 
              FORMAT JSON
          )
          FORMAT JSON ABSENT ON NULL  
          RETURNING VARCHAR(200) FORMAT JSON
      ) ITEM_JSON  
    FROM PROD_T  
)
-- 赋值行数到leng
SELECT COUNT(*) INTO leng FROM BASE;

-- 再次使用BASE生成聚合JSON
WITH BASE AS ( 
    SELECT id, item, 
      JSON_OBJECT(
          'item' VALUE item, 
          'itemScore' VALUE itemScore,
          'stage' VALUE stage,
          'reco' VALUE JSON_OBJECT(
              'product' VALUE product,
              'url' VALUE url ,
              'score' VALUE score 
              FORMAT JSON
          )
          FORMAT JSON ABSENT ON NULL  
          RETURNING VARCHAR(200) FORMAT JSON
      ) ITEM_JSON  
    FROM PROD_T  
),  
PROD_OBJS AS ( 
    SELECT  JSON_OBJECT ( 
        KEY 'id' VALUE ID , 
        KEY 'itens' VALUE 
               JSON_ARRAY ( LISTAGG( ITEM_JSON , ', ') WITHIN GROUP (ORDER BY ITEM) FORMAT JSON )
        FORMAT JSON 
    ) json_objects  
    FROM BASE GROUP BY ID 
) 
SELECT JSON_ARRAY (SELECT json_objects FROM PROD_OBJS FORMAT JSON) INTO result 
FROM SYSIBM.SYSDUMMY1;

内容的提问来源于stack exchange，提问作者DB2fan