데이터베이스 질문과 답변
by 박현갑 [Oracle 에러코드] SYS_CONNECT_BY_PATH [2011.07.04 17:19:18]
아래처럼 오라클9i인데 쿼리가 안먹힙니다.
해결방안 좀 충고해주세요.
--------------------------------------------------------------------------------------------------------------------------
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Jul 4 17:17:12 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(tel_ofc_name, '/') "Path"
2 FROM tb_Ofc
3 START WITH tel_ofc_name = '백령지점'
4 CONNECT BY PRIOR tel_ofc_cd = p_tel_ofc_cd;
SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(tel_ofc_name, '/') "Path"
*
ERROR at line 1:
ORA-00904: "SYS_CONNECT_BY_PATH": invalid identifier
원하시는 기능을 함수로 직접 만드시거나 쿼리로 구현 하셔야 합니다.
--, SYS_CONNECT_BY_PATH(ename, '/') "Path"
, (SELECT MIN(DECODE(level, 5, '/' || ename))
|| MIN(DECODE(level, 4, '/' || ename))
|| MIN(DECODE(level, 3, '/' || ename))
|| MIN(DECODE(level, 2, '/' || ename))
|| MIN(DECODE(level, 1, '/' || ename))
FROM emp
START WITH empno = a.empno
CONNECT BY empno = PRIOR mgr
) "Path"
FROM emp a
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;
select * from v$version; 버전 확인해보세요.
제가 잘 못 알고 있었습니다...
혼란을 드려 죄송합니다....(--) (__)
) 버튼을 클릭하여 작성 하시면 됩니다.