oracle LISTAGG函数未组合行值

enyaitl3 于 2023-01-30 发布在 Oracle

关注(0)|答案(2)|浏览(197)

我从SQL查询中得到了以下数据（省略了其他列）。

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

正如您所看到的，3个行号中的每一个行号都有两个不同的HOLD_CODE值。我想要做的是使用LISTAGG函数来合并行，以便基于INVOICE_ID连接HOLD_CODE值（我相信INV_LINE_NUMBER也是如此）。
我的预期结果如下：

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以在HOLD_CODE列上使用LISTAGG函数，但是它没有按预期输出。下面是我添加的LISTAGG函数：

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)

结果是连接每行的每个HOLD_CODE，如下所示，这仍然导致输出6行，而不是3行。这不是我需要的：

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

同样，我的预期结果如下：

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

oracle

来源：https://stackoverflow.com/questions/75271225/listagg-function-not-combining-row-values

2条答案

按热度按时间

jm81lzqq1#

应该在查询外部而不是内部应用LISTAGG。它应该作用于当前输出，而不是它的子查询。

SELECT
 ai.invoice_id,
 nvl(ai.amount,0) invoice_amt,
 ai.line_number Inv_line_number,
    LISTAGG(SELECT decode(ahc1.postable_flag,'N',ah1.hold_lookup_code, '') AS decoded_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   ), ',') WITHIN 
      GROUP (ORDER BY decoded_code) 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)
GROUP BY ai.invoice_id,
         nvl(ai.amount,0) invoice_amt,
         ai.line_number Inv_line_number.
         <all non-aggregated fields in the outer query>

注意：不要在查询中留下<all non-aggregated fields in the outer query>：用非聚合字段替换它。

赞(0）回复(0）举报 2023-01-30

vdzxcuhz2#

从查询结果获取聚合结果的最简单方法是聚合查询结果：

select
  invoice_id, invoice_amt, inv_line_number,
  listagg(hold_code, ', ') within group (order by hold_code) as hold_codes
from ( <your query here> )
group by invoice_id, invoice_amt, inv_line_number
order by invoice_id, invoice_amt, inv_line_number;

赞(0）回复(0）举报 2023-01-30

我来回答

oracle LISTAGG函数未组合行值

2条答案

相关问题

热门标签

最新问答