Subquery获取错误:字符串连接的结果太长

Question

我有一个从产品中获取库存数据的查询，它需要在一个列中完成，所以我使用这个列表聚合查询(附加信息：Select subquery that return multiple rows as string (one column) in table (Oracle SQL))。但是在一些数据中得到了一些错误:字符串连接的结果太长了。如何优化此查询并解决此错误？(我只需要在这个子查询中更改，因为查询在其他函数中使用)谢谢

SELECT K.PRODUCT_NO, ..., (SELECT i.OPTIONS
    FROM PRODUCT p , ...
    LEFT JOIN (
        SELECT ITM.PRODUCT_NO
            ,LISTAGG(NVL2(ITM.OPTION1, ITM.OPTION1 || (chr(13)||chr(10)), '') || NVL2(ITM.OPTION2, ITM.OPTION2 || (chr(13)||chr(10)), '') || NVL2(ITM.OPTION3, ITM.OPTION3 || (chr(13)||chr(10)), '') || NVL2(ITM.OPTION4, ITM.OPTION4 || (chr(13)||chr(10)), ''), ',') WITHIN
        GROUP (
                ORDER BY ITM.itm_no
                ) AS OPTIONS
        FROM ITEM ITM
        GROUP BY ITM.PRODUCT_NO
        ) i ON (p.PRODUCT_NO = i.PRODUCT_NO)
    WHERE P.PRODUCT_NO = K.PRODUCT_NO) as OPTIONS,
    (SELECT i.OPTIONS
    FROM PRODUCT p
    LEFT JOIN (
        SELECT ITM.PRODUCT_NO
            ,LISTAGG(NVL2(QUANTITY, QUANTITY || (chr(13)||chr(10)), '0'))  WITHIN
        GROUP (
                ORDER BY ITM.itm_no
                ) AS OPTIONS
        FROM ITEM_INVENTORY ITM
        GROUP BY ITM.PRODUCT_NO
        ) i ON (p.PRODUCT_NO = i.PRODUCT_NO)
    WHERE P.PRODUCT_NO = K.PRODUCT_NO) as QTY
    from PRODUCT K

Answer 1

Xmlagg方法可用于聚合字符串。

举例说明。

select rtrim(
           xmlserialize(content extract(xmlagg(xmlelement(e, object_name || ',') order by object_id), '//text()')
                        no indent)
         , ',')
       , object_type
    from user_objects
group by object_type;

在您的示例中，第一个子查询将如下所示。

  select ITM.PRODUCT_NO
       , rtrim(
           xmlserialize(
             content extract(
                       xmlagg(
                         xmlelement(
                           e
                         ,    nvl2(ITM.OPTION1, ITM.OPTION1 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION2, ITM.OPTION2 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION3, ITM.OPTION3 || (chr(13) || chr(10)), '')
                           || nvl2(ITM.OPTION4, ITM.OPTION4 || (chr(13) || chr(10)), '')
                           || ',')
                         order by ITM.itm_no)
                     , '//text()')
             no indent)
         , ',')
    from ITEM ITM
group by ITM.PRODUCT_NO

在这里您可以找到更多的方法https://www.programmingmusings.com/index.php/2015/05/22/string-aggregation-in-oracle-sql/