전에 조인 관련 비슷한 질문을 올렸는데도 응용을 하지 못해 쿼리를 올립니다.

SELECT
    B.DNUM,
    DNAME,
    SUM(COALESCE(S0,0)) S0,
    SUM(COALESCE(S1,0)) S1,
    SUM(COALESCE(S2,0)) S2,
    SUM(COALESCE(S3,0)) S3,
    SUM(COALESCE(S4,0)) S4,
    SUM(COALESCE(S5,0)) S5
FROM
    (
        SELECT
            DAYOFWEEK(TO_DATE("DATETIME", 'YYYYMMDD')) DNUM,
            SEVERITY,
            ( CASE WHEN SEVERITY = '0' THEN SUM("COUNT") END) S0,
            ( CASE WHEN SEVERITY = '1' THEN SUM("COUNT") END) S1,
            ( CASE WHEN SEVERITY = '2' THEN SUM("COUNT") END) S2,
            ( CASE WHEN SEVERITY = '3' THEN SUM("COUNT") END) S3,
            ( CASE WHEN SEVERITY = '4' THEN SUM("COUNT") END) S4,
            ( CASE WHEN SEVERITY = '5' THEN SUM("COUNT") END) S5
        FROM
            IVU_T_OMN_EVCNT_D A
        WHERE
            "DATETIME" BETWEEN '20111030' AND '20111105'
        AND SEVERITY >= '0'
        AND
            (
                '' IN ('0','')
             OR A.HOSTNAME IN
                (
                    SELECT
                        NODE_ID
                    FROM
                        (
                            SELECT
                                NODE_ID,
                                PARENT_NODE_ID,
                                IS_GROUP,
                                LEVEL
                            FROM
                                IVU_V_GEN_HWTREE START
                            WITH NODE_ID CONNECT BY NODE_ID = PRIOR PARENT_NODE_ID
                            GROUP BY
                                NODE_ID
                            ORDER BY
                                LEVEL
                        ) AS AAA
                )
            )
        GROUP BY
            DAYOFWEEK(TO_DATE("DATETIME", 'YYYYMMDD')),
            SEVERITY
    )
    A
RIGHT OUTER JOIN (VALUES
    (1,'일'),(2,'월'),(3,'화'),(4,'수'),(5,'목'),(6,'금'),(7,'토')) B (DNUM, DNAME)
ON
    A.DNUM = B.DNUM
GROUP BY
    B.DNUM,
    B.DNAME

 

문제가 되는것이

RIGHT OUTER JOIN (VALUES
    (1,'일'),(2,'월'),(3,'화'),(4,'수'),(5,'목'),(6,'금'),(7,'토')) B (DNUM, DNAME)
ON
    A.DNUM = B.DNUM

인데 몇몇 답변올려주신것을 응용하려 햇지만 되지 않아 도움 청해 봅니다.

 

DNUM	DNAME	S0	S1	S2	S3	S4	S5
1	월
2	화
3	수
4	목
5	금
6	토
7	일

 

조인을 제외하면 DNUM,S0~ S5까지 표현하구요 조인을 통해  DNAME을 추가하려는게 추구하는 쿼리 입니다.