데이터베이스 질문과 답변
by 이정수 [2009.12.04 15:54:18]
요 에러의 원인이 정확히 뭔가요..;;;;
일단 네이버에서 찾아보면 9i와 10g의 버전차이라고만 나오네요..;;
단순히 버전 차이인거 같지는 않구요.. order siblings by 가 start with~ connect by와 같이 쓰이는게 맞나요??
그렇게 보이긴 하는데요.. 아닌거 같기도 하구요.. 여튼.. 제 쿼리가 서브쿼리들이 많구..
서브쿼리 중에 star with~ connect by 가 있구.. order by siblings를 가장 밖에 썼는데..저런 에러가
나더군요..;;원인이 네이버에서처럼 단순히 버전 차이인건지..아님 제가 쿼리를 잘못쓴건지..;;
대략 쿼리는
SELECT X1.ACCD_TP
,X2.ACCD_ACCD
,X2.ACCD_ACNA
,X2.ACCD_LEVL
,X2.ACCD_UPCD
,TO_NUMBER(SUBSTR(X2.AM_STR, 1, INSTR(X2.AM_STR, '|') -1 )) BE_ACBL_TDBL
,TO_NUMBER(SUBSTR(X2.AM_STR, INSTR(X2.AM_STR,'|')+1 , INSTR(X2.AM_STR,'|', 1, 2)-(INSTR(X2.AM_STR,'|')+1)) ) BOJUNG_DAMT
,TO_NUMBER(SUBSTR(X2.AM_STR, INSTR(X2.AM_STR,'|', 1, 2)+1, INSTR(X2.AM_STR,'|', 1, 3)-(INSTR(X2.AM_STR,'|', 1, 2)+1))) BOJUNG_CAMT
,TO_NUMBER(SUBSTR(X2.AM_STR, INSTR(X2.AM_STR,'|', 1, 3)+1, INSTR(X2.AM_STR,'|', 1, 4)-(INSTR(X2.AM_STR,'|', 1, 3)+1))) AF_ACBL_TDBL
,(DECODE(X2.ACCD_ACCD, '6120000001', '6120010101', '6120000002', '6120010102', ACCD_ACCD)) SEQ
FROM (
SELECT DISTINCT(SUBSTR(ACCD_ACCD,1,1)) ACCD_TP
FROM IFRS_FACOA
WHERE ACCD_LEVL = 1
) X1 LEFT JOIN (
SELECT ACCD_ACCD
,ACCD_ACNA
,ACCD_LEVL
,ACCD_UPCD
,ACCD_GRGU
,NVL((CASE WHEN ACCD_LEVL = 5 THEN (
SELECT NVL(SUM(BE_ACBL_TDBL), 0)||'|'||(NVL(SUM(AF_ACBL_DAMT),0) - NVL(SUM(BE_ACBL_DAMT), 0))||'|'||(NVL(SUM(AF_ACBL_CAMT), 0) - NVL(SUM(BE_ACBL_CAMT), 0))||'|'||NVL(SUM(AF_ACBL_TDBL), 0)||'|'
FROM IFRS_ASACBLPF AA1
WHERE ACBL_SAUP = '02'
AND ACBL_ACDT = '20090900'
AND ACBL_ACCD = A.ACCD_ACCD
)
WHEN ACCD_LEVL = 1 THEN (
SELECT NVL(SUM(BE_ACBL_TDBL), 0)||'|'||(NVL(SUM(AF_ACBL_DAMT),0) - NVL(SUM(BE_ACBL_DAMT), 0))||'|'||(NVL(SUM(AF_ACBL_CAMT), 0) - NVL(SUM(BE_ACBL_CAMT), 0))||'|'||NVL(SUM(AF_ACBL_TDBL), 0)||'|'
FROM IFRS_ASACBLPF AA1
WHERE ACBL_SAUP = '02'
AND ACBL_ACDT = '20090900'
AND ACBL_ACCD IN (
SELECT ACCD_ACCD
FROM IFRS_FACOA AA3
WHERE ACCD_ACCD LIKE SUBSTR(A.ACCD_ACCD,1,1)||'%'
AND ACCD_LEVL = 5
)
)
WHEN ACCD_LEVL IN (2,3,4) THEN (
SELECT NVL(SUM(BE_ACBL_TDBL), 0)||'|'||(NVL(SUM(AF_ACBL_DAMT),0) - NVL(SUM(BE_ACBL_DAMT), 0))||'|'||(NVL(SUM(AF_ACBL_CAMT), 0) - NVL(SUM(BE_ACBL_CAMT), 0))||'|'||NVL(SUM(AF_ACBL_TDBL), 0)||'|'
FROM IFRS_ASACBLPF AA2
WHERE ACBL_SAUP = '02'
AND ACBL_ACDT = '20090900'
AND ACBL_ACCD IN (
SELECT DISTINCT DECODE(ACCD_LEVL, 5, ACCD_ACCD)
FROM IFRS_FACOA A4
WHERE ACCD_ACCD LIKE SUBSTR(A.ACCD_ACCD,1,1)||'%'
START WITH A4.ACCD_UPCD = A.ACCD_ACCD
CONNECT BY PRIOR A4.ACCD_ACCD = A4.ACCD_UPCD
)
)
ELSE '9999999'
END), '0') AM_STR
,BANK_ACCD_ACCD
FROM IFRS_FACOA A
WHERE 1=1
AND ACCD_LEVL <= '5'
AND ACCD_ACCD < '5000000000'
) X2 ON X2.ACCD_ACCD LIKE X1.ACCD_TP||'%'
ORDER SIBLINGS BY A4.ACCD_GRGU
--ORDER BY SEQ
이렇습니다..;;
제가 쓴게 잘못 쓴건지..흠.;;;;
최대한 간단하게 생각하시고 작성하세요.
일단 제가 작성한 쿼리를 올려볼께요. 데이터도 없고 요구사항도 파악하기 힘들지만 계층쿼리의 기본개념만 가지고 작성해 봤습니다.
WITH t1 AS
(
SELECT accd_accd, accd_acna, accd_levl, accd_upcd, accd_grgu
FROM ifrs_facoa
WHERE accd_levl <= '5'
AND accd_accd < '5000000000'
)
, t2 AS
(
SELECT acbl_accd, be_acbl_tdbl, af_acbl_damt, be_acbl_damt, af_acbl_camt, be_acbl_camt, af_acbl_tdbl
FROM ifrs_asacblpf
WHERE acbl_saup = '02'
AND acbl_acdt = '20090900'
)
SELECT accd_accd, accd_acna, accd_levl, accd_upcd,
(
SELECT NVL(SUM(be_acbl_tdbl),0)
||'|'|| (NVL(SUM(af_acbl_damt),0) - NVL(SUM(be_acbl_damt),0))
||'|'|| (NVL(SUM(af_acbl_camt),0) - NVL(SUM(be_acbl_camt),0))
||'|'|| NVL(SUM(af_acbl_tdbl),0) am_str
FROM t1 b, t2 c
WHERE b.accd_accd = c.acbl_accd
START WITH b.accd_accd = a.accd_accd
CONNECT BY PRIOR b.accd_accd = b.accd_upcd
) am_str
FROM t1 a
START WITH accd_levl = '1'
CONNECT BY PRIOR accd_accd = accd_upcd
ORDER SIBLINGS BY accd_grgu
;
) 버튼을 클릭하여 작성 하시면 됩니다.