LISTAGG函数未正确合并行值的SQL技术求助

问题背景 #

现有发票查询数据，同一INVOICE_ID和INV_LINE_NUMBER对应多个不同的HOLD_CODE值，需要通过LISTAGG函数按这两个字段分组，将对应HOLD_CODE拼接为单个字段，同时合并重复行。

原始查询数据 #

INVOICE_ID        INVOICE_AMT   INV_LINE_NUMBER    HOLD_CODE
300000155983977   51403         1                  AMT ORD
300000155983977   51403         2                  AMT ORD
300000155983977   51403         3                  AMT ORD
300000155983977   51403         1                  MAX AMT ORD
300000155983977   51403         2                  MAX AMT ORD
300000155983977   51403         3                  MAX AMY ORD

期望结果 #

INVOICE_ID        INVOICE_AMT   INV_LINE_NUMBER    HOLD_CODE
300000155983977   51403         1                  AMT ORD, MAX AMT ORD
300000155983977   51403         2                  AMT ORD, MAX AMT ORD
300000155983977   51403         3                  AMT ORD, MAX AMT ORD

当前问题SQL及错误结果 #

当前SQL直接在主查询中关联ap_holds_all和ap_hold_codes，导致主查询先返回6行数据，子查询中的LISTAGG因错误关联外部表字段，出现重复拼接：

当前SQL #

SELECT
 ai.invoice_id,
 nvl(ai.amount,0) invoice_amt,
 ai.line_number Inv_line_number,
    (SELECT LISTAGG( decode(ahc1.postable_flag,'N',ah1.hold_lookup_code, '') , ',') WITHIN 
      GROUP (ORDER BY decode(ahc1.postable_flag,'N',ah1.hold_lookup_code, '') )
       FROM ap_hold_codes ahc1, ap_holds_all ah1
      WHERE ah1.hold_lookup_code = ahc1.hold_lookup_code
        AND ahc1.HOLD_LOOKUP_CODE = ahc.HOLD_LOOKUP_CODE
        AND ah1.HOLD_LOOKUP_CODE = ah.HOLD_LOOKUP_CODE 
        AND ah1.invoice_id = ah.invoice_id
        AND    ah1.invoice_id = ai.invoice_id
      GROUP BY  ah.invoice_id   , ai.invoice_num   ) hold_code
                                         
     --(Additional columns omitted for simplification)
FROM   (  select distinct
                        apexp.invoice_id
                      -- ,invoice_num
                                                                          ,( CASE 
          (apexp.source_type)
            WHEN 'INCMPLT_INV' THEN
              CASE SUBSTR(apexp.invoice_num, 0,8)
              WHEN 'Invalid-' THEN
               CASE SUBSTR(apexp.invoice_num,9,LENGTH(apexp.invoice_id))
              WHEN TO_CHAR(apexp.invoice_id) THEN
                  (SELECT displayed_field
                  FROM ap_lookup_codes
                  WHERE lookup_code = 'INVALID'
                  AND lookup_type   ='NLS TRANSLATION'
                  )
                ELSE
                  apexp.invoice_num
                END
              ELSE
                CASE SUBSTR(apexp.invoice_num, 0,10)
                WHEN 'Duplicate-' THEN
                  CASE SUBSTR(apexp.invoice_num,11,LENGTH(apexp.invoice_id))
                  WHEN TO_CHAR(apexp.invoice_id) THEN
                    (SELECT alc.displayed_field
                      ||':'
                      ||air.token_value2
                    FROM ap_lookup_codes alc,
                      ap_interface_rejections air
                    WHERE alc.lookup_code     ='DUPLICATE'
                    AND alc.lookup_type       ='NLS TRANSLATION'
                    AND air.reject_lookup_code='DUPLICATE INVOICE NUMBER'
                    AND air.invoice_id        =apexp.invoice_id
                    )
                  ELSE
                    apexp.invoice_num
                  END
                ELSE
                  apexp.invoice_num
                END
              END
            ELSE
              apexp.invoice_num
            END) AS invoice_num
                       ,apexp.invoice_date
                       ,apexp.invoice_currency_code
                       ,NVL(aida.amount,AILA.AMOUNT) amount
                       ,apexp.doc_sequence_value
                       ,apexp.voucher_num
                       ,apexp.org_id
                       ,apexp.party_id                -- fusion v1 UATR
                       ,aila.line_number line_number
                       ,aida.distribution_line_number distribution_line_number
                       ,aida.dist_code_combination_id dist_code_combination_id                     
                    from ap_period_close_excps_gt apexp 
                    ,AP_INVOICE_LINES_ALL aila
                    ,ap_invoice_distributions_all aida
                    where apexp.source_type in ( 'LINES_WITHOUT_DISTS'
                                               , 'UNACCT_DISTS'
                                               ,'UNACCT_PREPAY_HIST'                                               
                                               ,'INCMPLT_INV'  )
                                                
                         and apexp.invoice_id=aila.invoice_id   
                         and aila.invoice_id=aida.invoice_id(+)
                         and aida.INVOICE_LINE_NUMBER(+)=aila.LINE_NUMBER
                         and aila.line_type_lookup_code  in ('ITEM','FREIGHT')                       
                        and   (NVL(:G_SWEEP_NOW,'N') = 'N' OR (:G_SWEEP_NOW = 'Y' AND 
               apexp.process_status_flag = 'Y'))
                        ORDER BY aida.distribution_line_number
                                                                     )  ai
     ,ap_holds_all ah
     ,ap_hold_codes ahc
   WHERE ai.invoice_id = ah.invoice_id
     AND   ah.hold_lookup_code = ahc.hold_lookup_code    
     AND  (ah.release_lookup_code is null and
                      ahc.postable_flag = 'N'  and
                      ah.hold_lookup_code is not null and
                      alc.lookup_type = 'HOLD CODE' and
                      alc.lookup_code = ah.hold_lookup_code)

错误结果 #

INVOICE_ID        INVOICE_AMT   INV_LINE_NUMBER    HOLD_CODE
300000155983977   51403         1                  AMT ORD, AMT ORD, AMT ORD
300000155983977   51403         2                  AMT ORD, AMT ORD, AMT ORD
300000155983977   51403         3                  AMT ORD, AMT ORD, AMT ORD
300000155983977   51403         1                  MAX AMT ORD, MAX AMT ORD, MAX AMT ORD
300000155983977   51403         2                  MAX AMT ORD, MAX AMT ORD, MAX AMT ORD
300000155983977   51403         3                  MAX AMY ORD, MAX AMT ORD, MAX AMT ORD

修正方案 #

核心思路是提前对ap_holds_all和ap_hold_codes的数据按发票ID和发票行号分组聚合，再与发票行主数据关联，避免主查询先展开多行。

修正后的SQL #

SELECT
  ai.invoice_id,
  NVL(ai.amount, 0) invoice_amt,
  ai.line_number inv_line_number,
  hold_agg.hold_code
FROM (
  -- 原发票行子查询保持逻辑不变，优化连接语法为ANSI标准
  SELECT DISTINCT
    apexp.invoice_id,
    (CASE 
      WHEN apexp.source_type = 'INCMPLT_INV' THEN
        CASE SUBSTR(apexp.invoice_num, 0, 8)
          WHEN 'Invalid-' THEN
            CASE SUBSTR(apexp.invoice_num, 9, LENGTH(apexp.invoice_id))
              WHEN TO_CHAR(apexp.invoice_id) THEN
                (SELECT displayed_field
                 FROM ap_lookup_codes
                 WHERE lookup_code = 'INVALID'
                   AND lookup_type = 'NLS TRANSLATION')
              ELSE apexp.invoice_num
            END
          ELSE
            CASE SUBSTR(apexp.invoice_num, 0, 10)
              WHEN 'Duplicate-' THEN
                CASE SUBSTR(apexp.invoice_num, 11, LENGTH(apexp.invoice_id))
                  WHEN TO_CHAR(apexp.invoice_id) THEN
                    (SELECT alc.displayed_field || ':' || air.token_value2
                     FROM ap_lookup_codes alc
                     JOIN ap_interface_rejections air 
                       ON air.invoice_id = apexp.invoice_id
                     WHERE alc.lookup_code = 'DUPLICATE'
                       AND alc.lookup_type = 'NLS TRANSLATION'
                       AND air.reject_lookup_code = 'DUPLICATE INVOICE NUMBER')
                  ELSE apexp.invoice_num
                END
              ELSE apexp.invoice_num
            END
        END
      ELSE apexp.invoice_num
    END) AS invoice_num,
    apexp.invoice_date,
    apexp.invoice_currency_code,
    NVL(aida.amount, AILA.AMOUNT) amount,
    apexp.doc_sequence_value,
    apexp.voucher_num,
    apexp.org_id,
    apexp.party_id,
    aila.line_number line_number,
    aida.distribution_line_number distribution_line_number,
    aida.dist_code_combination_id dist_code_combination_id                     
  FROM ap_period_close_excps_gt apexp 
  JOIN AP_INVOICE_LINES_ALL aila 
    ON apexp.invoice_id = aila.invoice_id   
  LEFT JOIN ap_invoice_distributions_all aida 
    ON aila.invoice_id = aida.invoice_id 
    AND aida.INVOICE_LINE_NUMBER = aila.LINE_NUMBER
  WHERE apexp.source_type IN ('LINES_WITHOUT_DISTS', 'UNACCT_DISTS', 'UNACCT_PREPAY_HIST', 'INCMPLT_INV')
    AND aila.line_type_lookup_code IN ('ITEM', 'FREIGHT')                       
    AND (NVL(:G_SWEEP_NOW, 'N') = 'N' OR (:G_SWEEP_NOW = 'Y' AND apexp.process_status_flag = 'Y'))
  ORDER BY aida.distribution_line_number
) ai
LEFT JOIN (
  -- 提前按发票ID和发票行号聚合HOLD_CODE
  SELECT
    ah.invoice_id,
    ah.invoice_line_number,
    LISTAGG(ah.hold_lookup_code, ', ') WITHIN GROUP (ORDER BY ah.hold_lookup_code) hold_code
  FROM ap_holds_all ah
  JOIN ap_hold_codes ahc 
    ON ah.hold_lookup_code = ahc.hold_lookup_code
  WHERE ah.release_lookup_code IS NULL
    AND ahc.postable_flag = 'N'
    AND ah.hold_lookup_code IS NOT NULL
    -- 修正原SQL中未关联alc表的问题，用EXISTS验证HOLD_CODE合法性
    AND EXISTS (
      SELECT 1 
      FROM ap_lookup_codes alc
      WHERE alc.lookup_type = 'HOLD CODE'
        AND alc.lookup_code = ah.hold_lookup_code
    )
  GROUP BY ah.invoice_id, ah.invoice_line_number
) hold_agg 
  ON ai.invoice_id = hold_agg.invoice_id
  AND ai.line_number = hold_agg.invoice_line_number;

关键修正点 #

1. 聚合逻辑前置：新增hold_agg子查询，提前对ap_holds_all按invoice_id和invoice_line_number分组，用LISTAGG拼接HOLD_CODE，确保每个发票行仅返回一条聚合记录。
2. 移除主查询冗余关联：不再直接在主查询中关联ap_holds_all和ap_hold_codes，避免主查询先展开为多行数据。
3. 修正未关联表问题：原SQL中WHERE子句引用的alc表未关联，改为用EXISTS子查询验证HOLD_CODE的合法性。
4. 优化连接语法：将旧式逗号连接改为ANSI JOIN语法，提升SQL可读性和维护性。
5. 保留无HOLD_CODE的行：使用LEFT JOIN关联聚合后的hold数据，确保即使没有HOLD_CODE的发票行也能在结果中保留。

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