꿈꾸는 개발자, DBA 커뮤니티 구루비

JOIN 질문드립니다.

by OMIL [2016.08.18 17:46:19]

SELECT O.DEPTCODE, O.DRCODE, O.ACTDATE, O.PTNO, P.SNAME,
      SUM(AMT1+AMT2) AMT
FROM AMT      O,
      PATIENT P,
      Doctor   d
WHERE ACTDATE >= '2015-01-01'
AND ACTDATE <= '2015-01-31'
AND O.Ptno   = P.Ptno(+)
AND O.DRCODE = D.DRCODE(+)
GROUP BY O.ACTDATE, O.PTNO, P.SNAME,
         O.DEPTCODE, O.DRCODE

select DEPTCODE, DRCODE, ACTDATE, PTNO, SNAME from JUP
where ACTDATE between '2015-01-01' and '2015-01-31'

2번 테이블의 값에 1번의 SUM값인 AMT값을 넣으려고 하는데요.

두 테이블의 DEPTCODE, DRCODE, ACTDATE, PTNO, SNAME이 일치하는 곳에 해당 AMT값을 넣고,

일치하지 않은 곳은 null값으로 출력하려고 하는데

조언 좀 부탁드리겠습니다.

by 랑에1 [2016.08.18 17:55:01]

SELECT a.*, b.AMT
FROM  2번 a, 1번 b
WHERE a.DEPTCODE = b.DEPTCODE(+)
AND   a.DRCODE = b.DRCODE(+)
AND   a.ACTDATE = b.ACTDATE(+)
AND   a.PTNO = b.PTNO(+)
AND   a.SNAME = b.SNAME(+)

by OMIL [2016.08.19 11:53:26]

1.
SELECT DEPTCODE, DRCODE, ACTDATE, PTNO, SNAME
FROM TWOPD_JUPMST
WHERE ACTDATE BETWEEN '2015-01-01' AND '2015-01-31'
AND (DELMARK = ' ' OR DELMARK IS NULL)
AND DEPTCODE = 'ABC'
AND DRCODE = '1111'

2.
SELECT DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE,O.ERDEPTCODE) DEPTCODE,  
       DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE) DRCODE,
       O.ACTDATE, O.PTNO, P.SNAME,
       SUM(DECODE(O.BUN,'94',0,'95',0,'96',0,'98',0,'99',0,AMT1+AMT2)) AMTTOT
FROM  TWOPD_SLIP      O,
      TWBAS_PATIENT   P,
      VIEW_DOCTOR_ALL D
WHERE ACTDATE >= '2015-01-01'
AND ACTDATE <= '2015-01-31'
AND RTRIM(DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE, O.ERDEPTCODE)) = 'ABC'
AND RTRIM(DECODE(TRIM(NVL(O.ERDRCODE,'')),'', O.DRCODE, O.ERDRCODE)) = '1111'
AND O.PTNO = P.PTNO(+)
AND RTRIM(DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE)) = RTRIM(D.DRCODE(+))
GROUP BY O.ACTDATE, O.PTNO, P.SNAME,     
         DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE,O.ERDEPTCODE),
         DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE),
         D.DRNAME

3.
SELECT A.*, B.AMTTOT
FROM
(SELECT DEPTCODE, DRCODE, ACTDATE, PTNO, SNAME
  FROM TWOPD_JUPMST
  WHERE ACTDATE BETWEEN '2015-01-01' AND '2015-01-31'
  AND (DELMARK = ' ' OR DELMARK IS NULL)
  AND DEPTCODE = 'ABC'
  AND DRCODE = '1111') A,
(SELECT DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE,O.ERDEPTCODE) DEPTCODE,  
              DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE) DRCODE,
              O.ACTDATE, O.PTNO, P.SNAME,
              SUM(DECODE(O.BUN,'94',0,'95',0,'96',0,'98',0,'99',0,AMT1+AMT2)) AMTTOT
  FROM  TWOPD_SLIP      O,
             TWBAS_PATIENT     P,
             VIEW_DOCTOR_ALL D
  WHERE ACTDATE >= '2015-01-01'
  AND ACTDATE <= '2015-01-31'
  AND RTRIM(DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE, O.ERDEPTCODE)) = 'ABC'
  AND RTRIM(DECODE(TRIM(NVL(O.ERDRCODE,'')),'', O.DRCODE, O.ERDRCODE)) = '1111'
  AND O.PTNO = P.PTNO(+)
  AND RTRIM(DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE)) = RTRIM(D.DRCODE(+))
  GROUP BY O.ACTDATE, O.PTNO, P.SNAME,     
                   DECODE(TRIM(NVL(O.ERDEPTCODE,'')),'',O.DEPTCODE,O.ERDEPTCODE),
                   DECODE(TRIM(NVL(O.ERDRCODE,'')),'',O.DRCODE,O.ERDRCODE),
                   D.DRNAME) B
WHERE A.DEPTCODE = B.DEPTCODE(+)
AND   A.DRCODE = B.DRCODE(+)
AND   A.ACTDATE = B.ACTDATE(+)
AND   A.PTNO = B.PTNO(+)
AND   A.SNAME = B.SNAME(+)
ORDER BY A.PTNO

답변 감사합니다. 말씀해주신것을 참고로 3번을 작성해보았는데 매칭이 누락되는 부분이 있어 결과값이 다르게 나오는데 어디가 잘못됐까요?

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변