데이터베이스 질문과 답변
안녕하세요.. 초보자인데요..쿼리 부탁을 좀 드리겠습니다.
간단한거 같은데 막히네요..^^;;;
DAY_FR과 DAY_TO 사이를 검색해서 해당 B_AMT와 EX_AMT를 가져오는건데요..
조건에 없을경우 특정 또는 마지막?(61~ 90)에 값을 가져오게 하고 싶어서요..^^;;
WITH RATE AS
(
SELECT ’1’ AS CD, ’0’ AS DAY_FR, ’30’ AS DAY_TO, 0 AS B_AMT, 0 AS EX_AMT FROM DUAL
UNION ALL
SELECT ’1’ AS CD, ’31’ AS DAY_FR, ’60’ AS DAY_TO, 0 AS B_AMT, 10 AS EX_AMT FROM DUAL
UNION ALL
SELECT ’1’ AS CD, ’61’ AS DAY_FR, ’90’ AS DAY_TO, 300 AS B_AMT, 20 AS EX_AMT FROM DUAL
)
SELECT NVL(B_AMT, 0) AS B_AMT, NVL(EX_AMT, 0) AS E_AMT
FROM RATE
WHERE (’99’) BETWEEN DAY_FR AND DAY_TO
by 김강환
[2009.04.17 08:33:39]
:V_DAY 변수에 값을 넣어 가면서 테스트 해 보면 됩니다. RPAD는 DAY_FR는 최대 5자리를 넘지 않는다는 가정하에 만든 것입니다.
WITH RATE AS
(
SELECT '1' AS CD, '0' AS DAY_FR, '30' AS DAY_TO, 0 AS B_AMT, 0 AS EX_AMT FROM DUAL
UNION ALL
SELECT '1' AS CD, '31' AS DAY_FR, '60' AS DAY_TO, 0 AS B_AMT, 10 AS EX_AMT FROM DUAL
UNION ALL
SELECT '1' AS CD, '61' AS DAY_FR, '90' AS DAY_TO, 300 AS B_AMT, 20 AS EX_AMT FROM DUAL
)
SELECT
SUBSTR(MAX(FLAG||B_AMT),6) B_AMT
,SUBSTR(MAX(FLAG||EX_AMT),6) EX_AMT
FROM
(
SELECT CD,DAY_FR,DAY_TO,B_AMT,EX_AMT
,CASE WHEN :V_DAY BETWEEN TO_NUMBER(DAY_FR) AND TO_NUMBER(DAY_TO) THEN RPAD('B'||DAY_FR,5,'0') ELSE RPAD('A'||DAY_FR,5,'0') END FLAG
FROM RATE
)
WITH RATE AS
(
SELECT '1' AS CD, '0' AS DAY_FR, '30' AS DAY_TO, 0 AS B_AMT, 0 AS EX_AMT FROM DUAL
UNION ALL
SELECT '1' AS CD, '31' AS DAY_FR, '60' AS DAY_TO, 0 AS B_AMT, 10 AS EX_AMT FROM DUAL
UNION ALL
SELECT '1' AS CD, '61' AS DAY_FR, '90' AS DAY_TO, 300 AS B_AMT, 20 AS EX_AMT FROM DUAL
)
SELECT
SUBSTR(MAX(FLAG||B_AMT),6) B_AMT
,SUBSTR(MAX(FLAG||EX_AMT),6) EX_AMT
FROM
(
SELECT CD,DAY_FR,DAY_TO,B_AMT,EX_AMT
,CASE WHEN :V_DAY BETWEEN TO_NUMBER(DAY_FR) AND TO_NUMBER(DAY_TO) THEN RPAD('B'||DAY_FR,5,'0') ELSE RPAD('A'||DAY_FR,5,'0') END FLAG
FROM RATE
)
by 질문자
[2009.04.17 10:06:57]
알려주셔서 감사합니다. 즐거운 금요일 되세요..^^*
) 버튼을 클릭하여 작성 하시면 됩니다.