데이터베이스 질문과 답변
Oracle8에서 SYS_CONNECT_BY_PATH 와 동일한 기능 있나요?
0
3
2,533
by 문보선 [2009.09.28 17:40:22]
아래와 같이 실행하면..
"aaaa.txt, bbbb.txt, cccc.txt" 로 표시되자나요.
SELECT SUBSTR (MAX(SYS_CONNECT_BY_PATH(gubun2, ', ')), 2) path#
FROM (
select '1' gubun1, 'aaaa.txt' gubun2, '1' cnt from dual union all
select '1' gubun1, 'bbbb.txt' gubun2, '2' cnt from dual union all
select '1' gubun1, 'cccc.txt' gubun2, '3' cnt from dual
)
START WITH cnt = 1
CONNECT BY PRIOR cnt = cnt - 1;
위와 같은 결과를 Oracle 8에서 실행할려면 어떻게 해야 하나요.
도움 부탁드립니다.
by 러드
[2009.09.29 16:22:54]
WITH A AS
(
select '1' gubun1, 'aaaa.txt' gubun2, 1 cnt from dual union all
select '1' gubun1, 'bbbb.txt' gubun2, 2 cnt from dual union all
select '1' gubun1, 'cccc.txt' gubun2, 3 cnt from dual union all
select '2' gubun1, 'cccc.txt' gubun2, 1 cnt from dual
)
, TT AS
(
SELECT A.*
, CASE WHEN CNT = 1 THEN NULL ELSE L-1 END UP_L
FROM ( SELECT A.*
, ROWNUM L
FROM A
) A
)
SELECT MAX( NVL2( GB2_1, GB2_1, '' ) || NVL2( GB2_1, ',', '' ) ||
NVL2( GB2_2, GB2_2 , '' ) || NVL2( GB2_2, ',', '' ) ||
NVL2( GB2_3, GB2_3, '' )) PATH
FROM ( SELECT T1.GUBUN1 GB_1
, T2.GUBUN1 GB_2
, T3.GUBUN1 GB_3
, T1.GUBUN2 GB2_1
, T2.GUBUN2 GB2_2
, T3.GUBUN2 GB2_3
FROM TT T1
, TT T2
, TT T3
WHERE T2.UP_L = T1.L(+)
AND T3.UP_L = T2.L(+)
) a
GROUP BY GB_1
(
select '1' gubun1, 'aaaa.txt' gubun2, 1 cnt from dual union all
select '1' gubun1, 'bbbb.txt' gubun2, 2 cnt from dual union all
select '1' gubun1, 'cccc.txt' gubun2, 3 cnt from dual union all
select '2' gubun1, 'cccc.txt' gubun2, 1 cnt from dual
)
, TT AS
(
SELECT A.*
, CASE WHEN CNT = 1 THEN NULL ELSE L-1 END UP_L
FROM ( SELECT A.*
, ROWNUM L
FROM A
) A
)
SELECT MAX( NVL2( GB2_1, GB2_1, '' ) || NVL2( GB2_1, ',', '' ) ||
NVL2( GB2_2, GB2_2 , '' ) || NVL2( GB2_2, ',', '' ) ||
NVL2( GB2_3, GB2_3, '' )) PATH
FROM ( SELECT T1.GUBUN1 GB_1
, T2.GUBUN1 GB_2
, T3.GUBUN1 GB_3
, T1.GUBUN2 GB2_1
, T2.GUBUN2 GB2_2
, T3.GUBUN2 GB2_3
FROM TT T1
, TT T2
, TT T3
WHERE T2.UP_L = T1.L(+)
AND T3.UP_L = T2.L(+)
) a
GROUP BY GB_1
by 호야
[2009.09.29 16:34:16]
dddd.txt 로우 하나 추가 하니.. bbb~ ddd 만 나오네욤..
로우가 100라면.. '||' 구분자를 써서 100개는 만들어야 할듯...
어렵다 어려워...
로우가 100라면.. '||' 구분자를 써서 100개는 만들어야 할듯...
어렵다 어려워...
by 러드
[2009.09.29 16:44:27]
ㅋㅋㅋ 함수가 좋을 느낌이에요!! 찾아보고 따라는 해봤지만 ㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.