언제 써먹게 될지 모르지만 정리해봅니다.
관련지식 : LTRIM, START WITH CONNECT BY, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT
10g이상에서는 바로 CONNECT_BY_ROOT로 구할 수 있는가 봅니다.
9i에서 사용시 참고하면 될듯합니다.
start with절에 지정한 필드가 계층적으로 펼쳐지는 것이므로 여기서 해당 필드 사이즈 만큼 substring하면 됩니다.
ltrim은 첫문자인 '/' 을 없애주는 기능
/* SYS_CONNECT_BY_PATH 를 이용하여 자른다.. ^^&--^^ */
SELECT LEVEL,
SUBSTR(LTRIM(SYS_CONNECT_BY_PATH(HOLIDAY_START_DATE, '/'),'/'),1,8) AS ROOT_ID , --holiday_start_date필드길이만큼 자른다. 1,8
LTRIM(SYS_CONNECT_BY_PATH(HOLIDAY_START_DATE, '/'),'/') AS ROOT_PATH , --전체 계층구조를 보여준다.
HOLIDAY_START_DATE, NEXT_DATE
FROM (
SELECT HOLIDAY_START_DATE,
TO_CHAR(TO_DATE(HOLIDAY_START_DATE,'YYYYMMDD')+1,'YYYYMMDD') AS NEXT_DATE
FROM TB_BUPAWEEKDAY
WHERE HOLIDAY_START_DATE LIKE '2013%'
)
START WITH HOLIDAY_START_DATE LIKE '201309%'
CONNECT BY PRIOR NEXT_DATE = HOLIDAY_START_DATE ;
LEVEL ROOT_ID ROOT_PATH HOLIDAY_START_DATE NEXT_DATE
1 20130901 20130901 20130901 20130902
1 20130907 20130907 20130907 20130908
2 20130907 20130907/20130908 20130908 20130909
1 20130908 20130908 20130908 20130909
1 20130914 20130914 20130914 20130915
2 20130914 20130914/20130915 20130915 20130916
1 20130915 20130915 20130915 20130916
1 20130918 20130918 20130918 20130919
2 20130918 20130918/20130919 20130919 20130920
3 20130918 20130918/20130919/20130920 20130920 20130921
4 20130918 20130918/20130919/20130920/20130921 20130921 20130922
5 20130918 20130918/20130919/20130920/20130921/20130922 20130922 20130923
1 20130919 20130919 20130919 20130920
2 20130919 20130919/20130920 20130920 20130921
3 20130919 20130919/20130920/20130921 20130921 20130922
4 20130919 20130919/20130920/20130921/20130922 20130922 20130923
1 20130920 20130920 20130920 20130921
2 20130920 20130920/20130921 20130921 20130922
3 20130920 20130920/20130921/20130922 20130922 20130923
1 20130921 20130921 20130921 20130922
2 20130921 20130921/20130922 20130922 20130923
1 20130922 20130922 20130922 20130923
1 20130928 20130928 20130928 20130929
2 20130928 20130928/20130929 20130929 20130930
1 20130929 20130929 20130929 20130930
참고한 URL:
1. http://www.oracleclub.com/lecture/2223
2. http://www.oracleclub.com/lecture/2250
3. http://blog.naver.com/swordsman93?Redirect=Log&logNo=33405532
'ORACLE' 카테고리의 다른 글
오라클 파라메터 조회 (0) | 2013.12.28 |
---|---|
rawtohex 와 chr 사용 (0) | 2013.12.20 |
LTRIM의 재발견 (0) | 2013.04.04 |
28일 수업 (0) | 2013.03.28 |
MATERIALIZED VIEW 생성 (0) | 2013.03.28 |