scalar subquery에 subquery에 값 전달어떻게 하나요? 0 4 4,020

by 짱구 [SQL Query] scalar subquery 복합행 단일행 [2012.02.29 12:43:05]


scalar subquery 작성했는데요.

SELECT (
   SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (NAME, ',')), 2) NAME
  FROM (
  SELECT NAME
 ,  ROW_NUMBER() OVER (ORDER BY T1.ID) rnum
 FROM TABLE1 T1, TABLE2 T2
  WHERE T1.ID = T2.ID
    AND T1.TOP_ID = TT.TOP_ID)
START WITH rnum = 1
    CONNECT BY PRIOR rnum = rnum - 1) NAME
  FROM TABLE TT
WHERE TT.TOP_ID = '1'

제가 하고자 하는일은 TT.TOP_ID가 1인 행이 여러행입니다. 이 여러행에 NAME을 합쳐서
만약 데이터가 아래와 같다면
-------------
안녕
친구
반가워
-------------

이를
-----------------------------
안녕, 친구, 반가워
------------------------------
만들고 싶습니다.

그래서 알아낸게 SYS_CONNECT_BY_PATH 인데 scalar subquery 에 subquery 에
TT.TOP_ID 값을 전달 하려다 보니 scalar subquery에 subquery에서 인식을 못하네요.
접근 LEVEL 차수 한계가 있는듯싶습니다.
고수님들 아래의 문장 어떻게 바꾸워야 하나요?
이중 조건은
1.
scalar subquery를 써야 하고
2. scalar subquery에 subquery 내에 AND T1.TOP_ID = 이부분에 '1' 값을 매핑을 못합니다.
그래서 꼭 TT.TOP_ID 값을 전달하는 방식을 알아야 할것같습니다.
감사합니다.





by 마농 [2012.02.29 14:31:55]
서브쿼리의 서브쿼리로 메인쿼리 조건을 줄수는 없습니다. 인식을 못하지요.
한단계 밖으로 조건을 빼내더라도 조건절이 자동으로 침투하여 부분범위로 처리되도록 할수도 있긴 합니다.
다만 이와같이 Row_number 가 들어간 경우 서브쿼리 안으로 조건이 침투하질 못합니다.
따라서 조건을 밖으로 빼내어 실행은 가능하지만 전체범위처리를 하게 되어 비효율이 발생하게 됩니다.

다음은 조건을 밖으로 빼낸 예입니다.
부분범위가 아닌 전체범위처리이므로
- row_number 할때도 Partition 구문이 추가됩니다.
- connect by 할때도 PRIOR top_id = top_id 조건이 추가됩니다.
tt.top_id 조건은 where 가 아닌 start with 절에 주게 됩니다.

SELECT tt.top_id,
(
SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(name, ',')), 2)
FROM
(
SELECT t1.top_id, t1.id, t2.name
, ROW_NUMBER() OVER(PARTITION BY t1.top_id ORDER BY t1.id) rnum
FROM table1 t1, table2 t2
WHERE t1.id = t2.id
)
START WITH rnum = 1 AND top_id = tt.top_id
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR top_id = top_id
) name
FROM table0 tt
WHERE tt.top_id = '1'
;

따라서 위 구문은 추천하고 싶지 않네요...
Sys_Connect_By_Path 대신 XmlAgg 또는 ListAgg 방법 추천합니다.
wm_concat 방법도 있습니다만. 정렬기능이 없습니다.

SELECT tt.top_id,
(
SELECT SUBSTR(XMLAGG(XMLELEMENT(x, ',', t2.name) ORDER BY t1.id).EXTRACT('//text()'), 2) --9i
--SELECT wm_concat(t2.name) --10g
--SELECT LISTAGG(t2.name, ',') WITHIN GROUP(ORDER BY t1.id) --11g
FROM table1 t1, table2 t2
WHERE t1.id = t2.id
AND t1.top_id = tt.top_id
) name
FROM table0 tt
WHERE tt.top_id = '1'
;

by 당직하사 [2012.02.29 14:35:08]
SCARAR SUBQUERY를 꼭 사용하지 않아도 될듯 보이는데.. 테이블 3개를 조인거시고..
SELECT LTRIM (SYS_CONNECT_BY_PATH (A.NAME, ','), ',') NAME
FROM (SELECT T1.ID
, T1.NAME
, ROW_NUMBER () OVER (PARTITION BY T1.ID ORDER BY T1.ID) RNUM
, COUNT (*) OVER (PARTITION BY T1.ID) CNT
FROM (SELECT '1' AS TOP_ID FROM DUAL
UNION ALL
SELECT '2' AS TOP_ID FROM DUAL) TT
, (SELECT '1' AS ID
, '안녕' NAME
FROM DUAL
UNION ALL
SELECT '1' AS ID
, '반가워' NAME
FROM DUAL
UNION ALL
SELECT '1' AS ID
, '친구들' NAME
FROM DUAL) T1
, (SELECT '1' AS ID FROM DUAL) T2
WHERE TT.TOP_ID = T1.ID
AND TT.TOP_ID = T2.ID
AND TT.TOP_ID = '1') A
WHERE LEVEL = CNT
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1
AND PRIOR A.ID = A.ID

이런식으로 처리해도 될거 같네요..

by 짱구 [2012.02.29 16:50:29]
마농님 당직하사님 감사합니다.

그렇지 않아도 우연찮게 XmlAgg,XMLELEMENT 를 찾아서 해결하고
혹시나 하는 마음에 여기다 제가 한거 올리려 했는데 마농님께서 이미 해주셨네요 ㅋㅋ

한가지 추가하자면.. XMLELEMENT 하면 한글이 깨져서 나오길래..

SUBSTR(xmlagg( xmlelement(TAG, ',' || T.TAG_NAME)).extract('//text()').getStringVal(),2)
즉 .getStringVal() 부분을 추가하였습니다.
그리고 XMLAGG가 order by를 줄수 있군요..감사합니다.

by 손님 [2012.02.29 23:22:36]

xmlagg로 처리하실때 주의할점은
xml함수를 사용하면 xml파싱이 일어나므로 한글이나 "<" ">" 등의 값들이 제대로 안나올수 있습니다.
만약 11g를 사용하신다면 listagg를 사용하는게 낫습니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입