Issue with SYS_CONNECT_BY_PATH

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Thu, 18 Oct 2012 12:24:43 -0300
Message-ID: <CAJdDhaOin7NoPHyYW7Mv1mmt9PWvcFYw0Fy4XC=Krhm0fAxW0A_at_mail.gmail.com>

Hi friends,
1.) I would like to understand why this query is not working properly in this situation:

26 \n1: ALIM-MET-1*1-5
26 \n1: ALIM-MET-1*1-5,\n(6: 5XD+)

I have the same element_id (26) twice.

2.) The internal query returns it to be concatenated:

\n1: ALIM-MET-1*1-5 26 1 1

\n(6: 5XD+)                 26 1) 6

I understand that it should return only 1 line

26 \n1: ALIM-MET-1*1-5,\n(6: 5XD+)

What is wrong ?

select

   element_id,
   substr(SYS_CONNECT_BY_PATH(par, ','),2) name_list from

   (
   select distinct

          todos.element_id
   ,      todos.par
   ,      count(*) OVER ( partition by todos.element_id ) cnt,
          ROW_NUMBER () OVER ( partition by todos.element_id order by
todos.element_id, todos.initial_unit
     ) seq

   from
    (

     SELECT
        CASE
          WHEN result.situation = 1
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '


            || table1.num

            || NVL2(table1.lat, '_at_'

            || table1.lat, NULL)

            || '*'

            || result.low_pair

            || '-'

            || result.high_pair

            || DECODE (result.status, 3, ')', '')

          WHEN result.situation= 2
          AND sequence            <> 1
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '
            ||'+'
            ||((result.high_pair-result.low_pair)+1)


            || 'XD'

            || DECODE (result.status, 3, ')', '')

          WHEN result.situation= 2
          AND sequence             = 1
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '
            ||((result.high_pair-result.low_pair)+1)
            ||'XD'
            ||'+'


            || DECODE (result.status, 3, ')', '')

          WHEN result.situation= 3
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '


            || table1.num

            || NVL2(table1.lat, '_at_'

            || table1.lat, NULL)

            || '*'

            || result.low_pair

            || '-'

            || result.high_pair

            || ' '

            || 'LD'

            || DECODE (result.status, 3, ')', '')

          WHEN result.situation= 4
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '


            || table1.num

            || NVL2(table1.lat, '_at_'

            || table1.lat, NULL)

            || '*'

            || result.low_pair

            || '-'

            || result.high_pair

            || ' '

            || 'Carrier'

            || DECODE (result.status, 3, ')', '')

          WHEN result.situation= 5
          THEN DECODE (result.status, 3, '\n(', '\n')


            || result.initial_unit

            ||': '


            || table1.num

            || NVL2(table1.lat, '_at_'

            || table1.lat, NULL)

            || '*'

            || result.low_pair

            || '-'

            || result.high_pair

            || ' '

            || 'PCM'

            || DECODE (result.status, 3, ')', '')

        END par ,
        result.element_id,
        result.sequence || DECODE (result.status, 3, ')', ''),
        initial_unit
      FROM vw_result_range result ,
           cable
     WHERE result.cable_id   = table1.id(+)
       AND result.element_type = 1
     ORDER BY element_id
     ,        initial_unit
     ,        sequence

   ) todos
)

where             seq=cnt
start with        seq=1
connect by prior  seq+1=seq
and prior         element_id=element_id

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 18 2012 - 17:24:43 CEST