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,,
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.
0
slightwv (䄆 Netminder) 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?
0
srikoteshAuthor Commented:
HI SLIGHTWV,

LISTAGG (CHRG_AMT, ',')

I HAVE TRIED LIKE BELOW

XMLAGG(CHRG_AMT, ',')
STRINGAGG(CHRG_AMT, ',')
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.