데이터베이스 질문과 답변

[답변][답변]실행이 안되네요 ㅠ.ㅠ 오라클을 잘몰라서... 0 4 1,843

by 백해현 [2006.10.26 14:10:50]

Select DER.*
     , C01+C02+C03+C04+C05+C06+C07+C08+C09+C10+C11+C12+C13+C14+C15+C16+C17+C18+C19+C20+C21+C22+C23+C24+C25+C26+C27+C28+C29+C30+C31 as TOTAL
  From
     ( Select AGENT_ID                                          as X_NAME
            , NVL(AGENT_NM, AGENT_ID)                           as X_VALUE
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '01', 1, 0))   as C01
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '02', 1, 0))   as C02
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '03', 1, 0))   as C03
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '04', 1, 0))   as C04
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '05', 1, 0))   as C05
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '06', 1, 0))   as C06
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '07', 1, 0))   as C07
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '08', 1, 0))   as C08
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '09', 1, 0))   as C09
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '10', 1, 0))   as C10
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '11', 1, 0))   as C11
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '12', 1, 0))   as C12
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '13', 1, 0))   as C13
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '14', 1, 0))   as C14
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '15', 1, 0))   as C15
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '16', 1, 0))   as C16
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '17', 1, 0))   as C17
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '18', 1, 0))   as C18
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '19', 1, 0))   as C19
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '20', 1, 0))   as C20
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '21', 1, 0))   as C21
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '22', 1, 0))   as C22
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '23', 1, 0))   as C23
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '24', 1, 0))   as C24
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '25', 1, 0))   as C25
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '26', 1, 0))   as C26
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '27', 1, 0))   as C27
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '28', 1, 0))   as C28
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '29', 1, 0))   as C29
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '30', 1, 0))   as C30
            , SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '31', 1, 0))   as C31
         From TRECORD A
        Where A.RFILE_NAME is not null
          and A.R_S_DATE <> ''
          and A.R_S_DATE >= '20061026'
          and A.R_S_DATE <= '20061026'
        Group By AGENT_ID END
               , NVL(AGENT_NM, AGENT_ID)
     ) DER
 Order By X_NAME

-- 이렇게 해보시구요.. 안되면 다시 올려 주세요

by 김경은 [2006.10.26 00:00:00]
에러가 나네요..ㅠ.ㅠ
간추려서 Select DER.*,C01+C31 as TOTAL
From
( Select AGENT_ID as X_NAME
, NVL(AGENT_NM, AGENT_ID) as X_VALUE
, SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '01', 1, 0)) as C01
, SUM(DECODE(SUBSTR(R_S_DATE, 7, 2), '31', 1, 0)) as C31
From TRECORD A
Where A.RFILE_NAME is not null
and A.R_S_DATE ''
and A.R_S_DATE >= '20061026'
and A.R_S_DATE <= '20061026'
Group By AGENT_ID END
, NVL(AGENT_NM, AGENT_ID)
) DER
Order By X_NAME

이렇게 했는데도 에러나요...왜그런지 도통 모르겠어요...ㅠ.ㅠ
by 백해현 [2006.10.26 00:00:00]
에러내용을 올려 주세요
by 백해현 [2006.10.26 00:00:00]
Group By AGENT_ID END
=> Group By AGENT_ID

이렇게 고치세요
by 김경은 [2006.10.26 00:00:00]
와우..너무 감사합니다^^

너무 좋으신 분들 많네요 여기~~

오라클 공부조 마니 해야겠네요 저는..

감사해요~^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입