what is the alternative for LISTAGG function in 10g oracle

srikotesh
srikotesh used Ask the Experts™
on
Hi Experts,

Below query is used LISTAGG() which is supporting in 11g .
same query is not executing in 10g  showing error -ORA00923-FROM Keyword not found.

what is the alternative for this query in 10g
i have tried with xmlagg(),and stringagg() but not succeeded.

Please provide the alternative solution.

query:
=====
SELECT DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE,
  LISTAGG (CHRG_AMT, ',') WITHIN GROUP (
ORDER BY CHRG_AMT) CHRG_AMT
FROM
  (SELECT S.DEAL_ID,
    MAX (DOC_ID) DOC_ID,
    SUM (CHRG_AMT)
    || ' '
    || CHRG_CURR_CODE CHRG_AMT,
    MAX (CHRG_MARGIN_RATE) CHRG_MARGIN_RATE
  FROM TR_CHARGES C,
    TR_SUMMARY S
  WHERE C.owner_cust_id IN ('10033')
  AND C.DEAL_ID      = S.DEAL_ID
  GROUP BY S.DEAL_ID,
    CHRG_CURR_CODE
  ) C
GROUP BY DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE;

Open in new window


Thanks,,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

Commented:
There is no built-in equivalent in Oracle10 to that command which Oracle introduced in Oracle11.  If you want that functionality in Oracle10, you will have to write a PL\SQL function that uses a cursor loop to process multiple records, then concatenate them in the order you want with the separator character you want, then return that concatenated string as the return value from your function.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>i have tried with xmlagg(),and stringagg() but not succeeded.

I can't remember 10g but I would suggest the XMLAGG method.  What did you try and what failed?

Author

Commented:
HI SLIGHTWV,

LISTAGG (CHRG_AMT, ',')

I HAVE TRIED LIKE BELOW

XMLAGG(CHRG_AMT, ',')
STRINGAGG(CHRG_AMT, ',')
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Information Technology Specialist
Commented:
It's been a while for me since 10g, but I think the xmlagg worked like this -
SELECT deal_id, chrg_margin_rate,
XMLAGG(XMLELEMENT(E, charg_amt||',')).EXTRACT('//text()') chrg_amt
FROM
  (SELECT S.DEAL_ID,
    MAX (DOC_ID) DOC_ID,
    SUM (CHRG_AMT)
    || ' '
    || CHRG_CURR_CODE CHRG_AMT,
    MAX (CHRG_MARGIN_RATE) CHRG_MARGIN_RATE
  FROM TR_CHARGES C,
    TR_SUMMARY S
  WHERE C.owner_cust_id IN ('10033')
  AND C.DEAL_ID      = S.DEAL_ID
  GROUP BY S.DEAL_ID,
    CHRG_CURR_CODE
  ) C
GROUP BY DEAL_ID,
  DOC_ID,
  CHRG_MARGIN_RATE;

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial