Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Location
    islamabad
    Posts
    9

    Thumbs up Unanswered: wm_concat:exceed limit error

    select distinct regno,wm_concat(REGISTRATION_NO) from test
    where dept like 'dc%'
    group by regno;

    this give me result when data is small but when i execute the query on larger table then it give s me xceed limit error so some one suggest me that for this u have to create procedure so kindly if some one knows then write procedure here

    waiting for urs response

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool sys_connect_by_path

    You could try SYS_CONNECT_BY_PATH or the XMLAGG(XMLELEMENT()) functions (of which I'm not an expert).

    Here is a SYS_CONNECT_BY_PATH example:
    Code:
    COL codes format a15
    SELECT     customer_id,
               SUBSTR (MAX (SYS_CONNECT_BY_PATH (code, ',')), 2) codes
          FROM (SELECT customer_id, code,
                       ROW_NUMBER () OVER (PARTITION BY customer_id ORDER BY code)
                                                                               rn
                  FROM customer_codes)
    START WITH rn = 1
    CONNECT BY PRIOR rn = rn - 1 AND PRIOR customer_id = customer_id
      GROUP BY customer_id;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •