I have the following data (additional columns omitted) from SQL query.
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
As you can see there are two different HOLD_CODE values for each of the 3 line numbers. What I want to do is combine the rows by using the LISTAGG function to concatenate the HOLD_CODE values based on the INVOICE_ID (and I believe INV_LINE_NUMBER as well)
My Desired Results would be the following:
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
I have modified the SQL to use the LISTAGG function on the HOLD_CODE column, however it is not outputting as expected. Below is the LISTAGG function I added:
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)
The results are concatenating each HOLD_CODE for each line as you can see below, which is still resulting in 6 rows being output, instead of 3. The is not what I need it to be:
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
Again my desired results would be the following:
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
CodePudding user response：
You should apply LISTAGG outside of your query, not inside. It should work on your current output, not on a subquery of it.
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>
Note: Don't leave <all non-aggregated fields in the outer query> in the query: substitute it with your non-aggregated fields.
CodePudding user response：
The simplest way to get from your query result to the aggregated result is to aggregate your query result:
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;