데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

몇칠째 삽질중 좀 도와주세요 고수님들 ㅜㅜ

작성자

박강수(hanker)

작성일

2006-09-25 14:21

조회수

1,481

select a.sitecd,a.sitenm,a.cmplplandtc,b.cdnm ,c.vchamt1 as 입고,c.sitecd from cy00001t a,
(select cdnm from crcodegt where cd in('M12414310101','M12414410100','M12414450169')) b,
(
select VCHAMT as vchamt1,sitecd from cma0312t
where acctcd1 in('M12414310101','M12414410100','M12414450169')
and vchline <> '000'
and vchdtc >='19960101'
and vchcls <>'M0915'
and vchcls like 'M2091%'
and vchdtc < '200605%'
group by vchamt,sitecd
) c
where a.cmplplandtc like '200605%'
and a.sitecd = c.sitecd
group by a.sitecd,a.sitenm,a.cmplplandtc,b.cdnm,c.vchamt1,c.sitecd

입고 금액은 대충 이렇게 해서 나오는것같은데 ...
쿼리를 한큐에 끝낼려니까~ 잘안돼네요 ㅜㅜ

박강수(hanker)님이 2006-09-25 14:22에 작성한 댓글입니다.

작성자의 Query에서 거의 답이 나오는 것으로 보이네요

select a.sitecd,a.sitenm,a.cmplplandtc,b.cdnm,
          case when c.vchamt1 is null then d.vchamt1
          else c.vchamt2 end "재고금액"
from   cy00001t a,crcodegt b,
         (select sum(VCHAMT) as VCHAMT1, sitecd from cma0312t
           where acctcd1 in('M12414310101','M12414410100','M12414450169')
              and vchline <> '000'
              and vchdtc >='19960101'
              and vchcls <>'M0915'
              and vchcls like 'M2091%'
              and vchdtc < '200605%'
           group by sitecd) c,
         (select sum(vchamt) as VCHAMT2,sitecd
            from cma0312t
          where acctcd2 in('M12414310101','M12414410100','M12414450169')
              and vchcls like 'M2092%'
              and vchdtc < '200605%'
              and vchdtc >='19960101'
              and vchcls <>'M0925'
              group by sitecd) d
where a.sitecd = b.sitecd
and    a.sitecd = c.sitecd(+)
and    a.sitecd = d.sitecd(+)

뭐 대충 이런 식으로 하면 되지 않을까요 ?
DB가 없어서 머리로만 짜서...ㅎㅎㅎ