SELECT DECODE(SA_SEQ,1,DENSE_RANK() OVER(ORDER BY SA_CTCODE))
NO,
DECODE(SA_SEQ,1,SA_CTCODE) SA_CTCODE,
DECODE(SA_SEQ,1,'기간','일반') SA_GUBUN,
DECODE(SA_SEQ,1,SA_NUM1,2,SA_NUM2) SA_NUM,
DECODE(SA_SEQ,1,SA_CHAMT1,2,SA_CHAMT2) SA_CHAMT,
DECODE(SA_SEQ,1,SA_CHAMT1,2,SA_CHAMT2) * 100 /
SUM(DECODE(SA_SEQ,1,SA_CHAMT1,2,SA_CHAMT2)) OVER(PARTITION BY NO)
SA_PERCENT
from table
바꾼것>
SELECT (CASE SA_SEQ WHEN 1 THEN
DENSE_RANK() OVER(ORDER BY SA_CTCODE)) AS NO,
(case SA_SEQ WHEN 1 THEN SA_CTCODE) AS SA_CTCODE,
(case SA_SEQ WHEN 1 THEN '기간','일반') AS SA_GUBUN,
(CASE SA_SEQ WHEN 1 THEN SA_NUM1
WHEN 2 THEN SA_NUM2
END) AS SA_NUM,
(CASE SA_SEQ WHEN 1 THEN SA_CHAMT1
WHEN 2 THEN SA_CHAMT2
END) AS SA_CHAMT,
(CASE SA_SEQ WHEN 1 THEN SA_CHAMT1
WHEN 2 THEN SA_CHAMT2
END) * 100 / SUM(CASE SA_SEQ WHEN 1 THEN
SA_CHAMT1
WHEN 2 THEN
SA_CHAMT2
END)
OVER(PARTITION BY NO) SA_PERCENT
아래 것을 바꾸면 어떻게 되나요?
FROM 절 다음인데요...
FROM (
SELECT SA_CTCODE,
COUNT(DISTINCT
DECODE(SUBSTR(SA_VDCODE,3,3),'999',NULL,SA_SEQ)) SA_NUM1,
NVL(SUM(DECODE(SUBSTR(SA_VDCODE,3,3),'999',TO_NUMBER(NULL),SA_CHAMT)),0)
SA_CHAMT1,
COUNT(DISTINCT
DECODE(SUBSTR(SA_VDCODE,3,3),'999',NO)) SA_NUM2,
NVL(SUM(DECODE(SUBSTR(SA_VDCODE,3,3),'999',SA_CHAMT)),0)
SA_CHAMT2
FROM SALELIB.SALETRS
마지막으로 NVL은 db2에서도 사용되는 함수인가요?
없다면 대치되는 함수는 무엇이 있을까요?
답변 부탁합니다.
|