asked on

what is the alternative for LISTAGG function in 10g oracle

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,,

Mark Geerlings

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.

slightwv (䄆 Netminder)

>>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?

srikotesh

ASKER

HI SLIGHTWV,

LISTAGG (CHRG_AMT, ',')

I HAVE TRIED LIKE BELOW

XMLAGG(CHRG_AMT, ',')
STRINGAGG(CHRG_AMT, ',')

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION