안녕하세요 오라클 시작한지 얼마 안된 초보자입니다. 프로시저를 짜는중인데 중복데이터 때문에 진행이 안됩니다. 그룹 바이는 표현식이 아니라고 그러는데 혹시 방법 아시는 분들 도와주세요~~~ 아래는 제가 나름대로 짜본 쿼리인데^^;;;; 많이들 도와주세요 열심히 배우겠습니다!!!! SELECT TPDS.POLL_CODE, TPMI.MANAGER_NM, TPMI.MANAGER_PHONENUM, TPI.POLL_NM, TPI.IMEI, TPMI.REGUSER_EMAIL,(BPM / (SELECT TO_NUMBER(SMALLCLASS_NM) FROM TVO_COMMON_CODE WHERE BIGCLASS_CODE = 'UDT' AND MIDDLECLASS_CODE = '001') * 100) BPM FROM TVO_POLL_DATA_SMS TPDS JOIN TVO_POLL_INFO TPI ON TPDS.POLL_CODE = TPI.POLL_CODE JOIN ( SELECT SUM(REC_BPM_ACC) + SUM(TRN_BPM_ACC) BPM, POLL_CODE FROM TVO_POLL_EQ_STATUS_STAT WHERE COLLECTION_DATE >= (select to_char(sysdate,'yyyymm') from dual)||'01' AND COLLECTION_DATE <= (select to_char(sysdate,'yyyymm') from dual)||'31' GROUP BY POLL_CODE ) TI ON TPI.POLL_CODE = TI.POLL_CODE JOIN TVO_POLL_MANAGER_INFO TPMI ON TI.POLL_CODE = TPMI.POLL_CODE;
SELECT TPD.POLL_CODE, TPM.MANAGER_NM, TPM.MANAGER_PHONENUM, TPI.POLL_NM
, TPI.IMEI, TPM.REGUSER_EMAIL, TPS.BPM/TCC.SMALLCLASS*100 AS BPM
FROM (SELECT DISTINCT POLL_CODE FROM TVO_POLL_DATA_SMS) TPD
INNER JOIN TVO_POLL_INFO TPI ON TPI.POLL_CODE = TPD.POLL_CODE
INNER JOIN TVO_POLL_MANAGER_INFO TPM ON TPM.POLL_CODE = TPD.POLL_CODE
INNER JOIN (
SELECT POLL_CODE
, NVL(SUM(REC_BPM_ACC),0)+NVL(SUM(TRN_BPM_ACC),0) AS BPM
FROM TVO_POLL_EQ_STATUS_STAT
WHERE COLLECTION_DATE BETWEEN TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD')
AND TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD')
GROUP BY POLL_CODE
) TPS ON TPS.POLL_CODE = TPD.POLL_CODE
CROSS JOIN (
SELECT NULLIF(TO_NUMBER(SMALLCLASS_NM),0) AS SMALLCLASS
FROM TVO_COMMON_CODE
WHERE BIGCLASS_CODE = 'UDT'
AND MIDDLECLASS_CODE= '001'
) TCC
;