Search Results for 'text()'

1 POSTS

  1. 2012|08 Oracle - 계층 구조 쿼리 응용, 가로 출력


# 계층 구조 조회
SQL > SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
            FROM emp
            START WITH job='PRESIDENT'
            CONNECT BY PRIOR empno = mgr
;

ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ------------------
KING                       7839            PRESIDENT
    JONES                  7566       7839 MANAGER
        SCOTT              7788       7566 ANALYST
            ADAMS          7876       7788 CLERK
        FORD               7902       7566 ANALYST
            SMITH          7369       7902 CLERK
    BLAKE                  7698       7839 MANAGER
        ALLEN              7499       7698 SALESMAN
        WARD               7521       7698 SALESMAN
        MARTIN             7654       7698 SALESMAN
        TURNER             7844       7698 SALESMAN
        JAMES              7900       7698 CLERK
    CLARK                  7782       7839 MANAGER
        MILLER             7934       7782 CLERK


# CONNECT_BY_ROOT
 - 상관관계 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 추출
SQL > SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
             CONNECT_BY_ROOT  empno "Root empno", level
             FROM emp
             START WITH job='PRESIDENT'
             CONNECT BY PRIOR empno = mgr
;

ENAME                     EMPNO Root empno      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839       7839          1
    JONES                  7566       7839          2
        SCOTT              7788       7839          3
            ADAMS          7876       7839          4
        FORD               7902       7839          3
            SMITH          7369       7839          4
    BLAKE                  7698       7839          2
        ALLEN              7499       7839          3
        WARD               7521       7839          3
        MARTIN             7654       7839          3
        TURNER             7844       7839          3
        JAMES              7900       7839          3
    CLARK                  7782       7839          2
        MILLER             7934       7839          3


# SYS_CONNECT_BY_PATH
 - 상관관계 쿼리에서 현재 로우 까지의 path 정보를 추출

SQL > COL path FORMAT A40

SQL > SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
             SYS_CONNECT_BY_PATH(ename, '/') "Path"
             FROM emp
             START WITH job='PRESIDENT'
             CONNECT BY PRIOR empno = mgr
;

ENAME                     EMPNO Path
-------------------- ---------- ----------------------------------------
KING                       7839 /KING
    JONES                  7566 /KING/JONES
        SCOTT              7788 /KING/JONES/SCOTT
            ADAMS          7876 /KING/JONES/SCOTT/ADAMS
        FORD               7902 /KING/JONES/FORD
            SMITH          7369 /KING/JONES/FORD/SMITH
    BLAKE                  7698 /KING/BLAKE
        ALLEN              7499 /KING/BLAKE/ALLEN
        WARD               7521 /KING/BLAKE/WARD
        MARTIN             7654 /KING/BLAKE/MARTIN
        TURNER             7844 /KING/BLAKE/TURNER
        JAMES              7900 /KING/BLAKE/JAMES
    CLARK                  7782 /KING/CLARK
        MILLER             7934 /KING/CLARK/MILLER



# CONNECT_BY_ISLEAF
 - 상관관계 쿼리에서 로우의 최하위 레벨 여부 확인(0=NO, 1=YES)
SQL > SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
             CONNECT_BY_ISLEAF "leaf", level
             FROM emp
             START WITH job='PRESIDENT'
             CONNECT BY NOCYCLE PRIOR empno = mgr
;

ENAME                     EMPNO       leaf      LEVEL
-------------------- ---------- ---------- ----------
KING                       7839          0          1
    JONES                  7566          0          2
        SCOTT              7788          0          3
            ADAMS          7876          1          4
        FORD               7902          0          3
            SMITH          7369          1          4
    BLAKE                  7698          0          2
        ALLEN              7499          1          3
        WARD               7521          1          3
        MARTIN             7654          1          3
        TURNER             7844          1          3
        JAMES              7900          1          3
    CLARK                  7782          0          2
        MILLER             7934          1          3


# 순환구조로 에러가 발생하여 계층쿼리를 사용하지 못할 경우

   NOCYCLE 을 주면 순환구조가 발생하기 전까지 계층구조를 전개한다.
   NOCYCLE 과 함께 CONNECT_BY_ISCYCLE 을 사용하면 순환구조가 발생여부를 알수 있다.
SQL > SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job,
            CONNECT_BY_ISCYCLE
            FROM emp
            START WITH job='PRESIDENT'
            CONNECT BY NOCYCLE PRIOR empno = mgr
;

ENAME                     EMPNO        MGR JOB                CONNECT_BY_ISCYCLE
-------------------- ---------- ---------- ------------------ ------------------
KING                       7839            PRESIDENT                           0
    JONES                  7566       7839 MANAGER                             0
        SCOTT              7788       7566 ANALYST                             0
            ADAMS          7876       7788 CLERK                               0
        FORD               7902       7566 ANALYST                             0
            SMITH          7369       7902 CLERK                               0
    BLAKE                  7698       7839 MANAGER                             0
        ALLEN              7499       7698 SALESMAN                            0
        WARD               7521       7698 SALESMAN                            0
        MARTIN             7654       7698 SALESMAN                            0
        TURNER             7844       7698 SALESMAN                            0
        JAMES              7900       7698 CLERK                               0
    CLARK                  7782       7839 MANAGER                             0
        MILLER             7934       7782 CLERK                               0



# 계층쿼리의 레벨 순위 별로 가로로 표시
 - MAX 그룹함수를 이용하여 최고값 하나만 추출
-- 그룹으로 찾아갈때 레벨기준 오름 차순 정렬(1, 2, 3, 4)
SQL > SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH( ename, ' / ' )),+4) AS ENAMES
            FROM emp
            START WITH job='PRESIDENT'
            CONNECT BY PRIOR empno = mgr
;

ENAMES
------------------------------
KING / JONES / SCOTT / ADAMS


 -- 하위 레벨 번호에서 상위로 찾아갈때 레벨 기준 내림 차순(4, 3, 2, 1)
SQL > SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH( ename, ' / ' )),+4) AS ENAMES
            FROM emp
            START WITH empno = 7876
            CONNECT BY PRIOR mgr = empno
;

ENAMES
------------------------------
ADAMS / SCOTT / JONES / KING


-- 레벨 기준 정렬( level ASC, DESC)
SQL > SELECT SUBSTR(XMLAGG(XMLELEMENT(x,'/',ename) ORDER BY level DESC).EXTRACT('//text()'), 2) AS ENMAEMS
            FROM emp
            START WITH empno = 7876
            CONNECT BY PRIOR mgr = empno
;

ENAMES
------------------------------
KING/JONES/SCOTT/ADAMS




참고 사이트 :
http://www.oracleclub.com/lecture/1903
http://www.oracleclub.com/article/53038



※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
08 20, 2012 20:18 08 20, 2012 20:18


Trackback URL : http://develop.sunshiny.co.kr/trackback/810

Leave a comment


Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. Generally I do not read post on bl... 레기읏룸 셔츠룸 차이. 레깅스룸 부엉이 01 24,
  2. Wonderful site. A lot of useful in... /427 01 23,
  3. 안녕하세요^^ 배그핵
  4. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  5. 정말 큰 도움이 되었습니다.. 감사합... 사랑은

Recent Trackbacks

  1. cabo packages cabo packages %M
  2. airbnb host insurance airbnb host insurance %M
  3. beaches in cabo beaches in cabo %M
  4. joe’s dj service joe’s dj service %M
  5. short term rental property insurance short term rental property insurance %M

Calendar

«   01 2020   »
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2824969 HIT
TODAY 588 HIT
YESTERDAY 443 HIT