Issue with SYS_CONNECT_BY_PATH
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Oct 2012 12:24:43 -0300
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, '@'
|| 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, '@'
|| 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, '@'
|| 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, '@'
|| 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
