데이터베이스 질문과 답변
서브쿼리에 서브쿼리 변수 전달방법 문의
1
6
3,251
by 쿼리를 정복하라 [2015.08.24 11:12:33]
WITH TA AS (
SELECT '20150801' AS COL , 'Y' AS GUBUN FROM DUAL UNION ALL
SELECT '20150810' AS COL , 'N' AS GUBUN FROM DUAL UNION ALL
SELECT '20150815' AS COL , 'Y' AS GUBUN FROM DUAL UNION ALL
SELECT '20150805' AS COL , 'N' AS GUBUN FROM DUAL
)
SELECT
COL,
(
SELECT
YYYYMMDD
--,TA.GUBUN --이부분 서브쿼리는 당연히 TA.GUBUN 값이 전달됩니다.
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY YYYYMMDD) RNUM,
YYYYMMDD
FROM HOLIDAY_TABLE
WHERE SLR_YMD > TA.COL --TA.COL 부분이 서브쿼리에 서브쿼리라 변수 전달이 안됩니다.
AND HLYDD_YN IS NULL
)
WHERE RNUM = DECODE(TA.GUBUN,'Y',10,20) -- 이부분은 TA.GUBUN 변수값이 전달됨 / TA.COL에 영업일 10일 또는 20일 이후날짜를 반환
) AS YYYYMMDD
FROM TA
;
소스와 같이 변수 전달이 어렵네요..
by 마농
[2015.08.24 13:47:04]
WITH holiday_table AS
(
SELECT TO_CHAR(TO_DATE('201501', 'yyyymm') + LEVEL - 1, 'yyyymmdd') slr_ymd
, CASE WHEN TO_CHAR(TO_DATE('201501', 'yyyymm') + LEVEL - 1, 'd') IN ('1','7')
THEN 'Y' END hlydd_yn
FROM dual
CONNECT BY LEVEL <= 365
)
, ta AS
(
SELECT '20150801' col, 'Y' gubun FROM dual
UNION ALL SELECT '20150810', 'N' FROM dual
UNION ALL SELECT '20150815', 'Y' FROM dual
UNION ALL SELECT '20150805', 'N' FROM dual
)
SELECT a.col
, a.gubun
, DECODE(a.gubun, 'Y', ymd10, ymd20) ymd
FROM ta a
, (SELECT slr_ymd
, LEAD(DECODE(hlydd_yn, '', slr_ymd), 10) IGNORE NULLS OVER(ORDER BY slr_ymd) ymd10
, LEAD(DECODE(hlydd_yn, '', slr_ymd), 20) IGNORE NULLS OVER(ORDER BY slr_ymd) ymd20
FROM holiday_table
) b
WHERE a.col = b.slr_ymd
;
by 쿼리를 정복하라
[2015.08.24 15:35:12]
답변감사합니다.
직접적인 해결은 아니지만 도움이 많이 되었습니다. (생각치도 못한 방법 감사합니다.)
TA 에 날짜는 임의로 편하게 작성하였는데 원래쿼리는 조인을 통해서 날짜를 예상할 수가 없습니다.
예제에는 2015년 8월로 기재했는데, 실제 셀렉트된 데이터는 2011년도 있고 2012년 2008년등 알 수가 없습니다.
그래서 서브쿼리에 서브쿼리를 원했는데 이방법은 안되는건가요?
휴일 테이블에 건수가 많아서 리스트건수 * 휴일테이블건수(약10만건) 이 부담부럽네요
by 마농
[2015.08.24 15:48:01]
WITH 구문 때문에 오해 하시는 것 같네요.
WITH 문은 단지 test 용 데이터를 만들어 내기 위한 것일 뿐입니다.
실제 적용시엔 WITH 가 없이 실 테이블(ta, holiday_table) 을 이용하시면 됩니다.
by 마농
[2015.08.24 15:49:08]
WITH holiday_table AS
(
SELECT TO_CHAR(TO_DATE('201501', 'yyyymm') + LEVEL - 1, 'yyyymmdd') slr_ymd
, CASE WHEN TO_CHAR(TO_DATE('201501', 'yyyymm') + LEVEL - 1, 'd') IN ('1','7')
THEN 'Y' END hlydd_yn
FROM dual
CONNECT BY LEVEL <= 365
)
, ta AS
(
SELECT '20150801' col, 'Y' gubun FROM dual
UNION ALL SELECT '20150810', 'N' FROM dual
UNION ALL SELECT '20150815', 'Y' FROM dual
UNION ALL SELECT '20150805', 'N' FROM dual
)
, t(col, gubun, cnt_limit, ymd, cnt) AS
(
SELECT a.col
, a.gubun
, DECODE(a.gubun, 'Y', 10, 20) cnt_limit
, b.slr_ymd ymd
, 0 cnt
FROM ta a
, holiday_table b
WHERE b.slr_ymd = a.col
UNION ALL
SELECT a.col
, a.gubun
, a.cnt_limit
, b.slr_ymd ymd
, a.cnt + DECODE(b.hlydd_yn, '', 1, 0) cnt
FROM t a
, holiday_table b
WHERE b.slr_ymd = TO_CHAR(TO_DATE(a.ymd, 'yyyymmdd') + 1, 'yyyymmdd')
AND a.cnt < cnt_limit
)
SELECT col, gubun, ymd
FROM t
WHERE cnt = cnt_limit
;
by 마농
[2015.08.24 15:52:28]
위의 쿼리가 LEAD 분석함수를 이용해 전체 휴일을 스캔 했다면
아래 쿼리는 이퀄(=) 검색으로 시작일자만 조회 한뒤
계층 전개 방식으로 종료일자를 찾아내는 방식입니다.
by 쿼리를 정복하라
[2015.08.24 17:56:02]
감사합니다. 도움이 많이 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.