jebyu_cd = 810은 테이블을 tlf_point_810을 따로쓰고
나머지는 tlf_point를 사용합니다
SELECT j.jehyu_cd as jehyu_cd, j.jehyu_name as jehyu_name, m.merch_no as merch_no, m.sangho as sangho, m.saup_no as saup_no,
m.merch_rank as merch_rank, m.ceo as ceo, m.regi_date as regi_date, m.bl_cd as bl_cd,
sum(decode(tran_cd,'M1',nvl(auth_amt,0),'U1',nvl(auth_amt,0),'H1',nvl(auth_amt,0),0))-sum(decode(tran_cd,'M2',nvl(auth_amt,0),'H2',nvl(auth_amt,0),0)) as save_amt,
sum(decode(tran_cd,'M1',1,'U1',1,0)) as save_cnt,
sum(decode(tran_cd,'M1',nvl(save_pnt,0),0)) as save_pnt,
sum(decode(tran_cd,'M3',1,0)) as use_cnt,
sum(decode(tran_cd,'M3',nvl(save_pnt,0),0)) as use_pnt,
sum(decode(tran_cd,'M1',nvl(save_pnt,0),0)) - sum(decode(tran_cd,'M2',nvl(save_pnt,0),0))
- sum(decode(tran_cd,'M3',nvl(save_pnt,0),0)) + sum(decode(tran_cd,'M4',nvl(save_pnt,0),0))
- sum(decode(tran_cd,'U3',nvl(save_pnt,0),0)) - sum(decode(tran_cd,'M9',nvl(save_pnt,0),0))
+ sum(decode(tran_cd,'M1',decode(status_cd,'00',nvl(add_save_pnt,0),0), 'H1',decode(status_cd,'00',nvl(add_save_pnt,0),0),0)) as remain_pnt
from jehyu_master j, merch_master m, tlf_point t
WHERE j.jehyu_cd = m.jehyu_cd AND t.merch_no(+) = m.merch_no
group by j.jehyu_cd, j.jehyu_name, m.merch_no, m.sangho, m.saup_no, m.merch_rank, m.ceo, m.regi_date, m.bl_cd
이렇게 실행하면 제휴사코드 810을 제외하고 나머지 제휴사의 가맹점별 포인트사용여부들을 출력해야되나 810 가맹점도 나옵니다 테이블은 분명 따로되어있습니다
SELECT j.jehyu_cd as jehyu_cd, j.jehyu_name as jehyu_name, m.merch_no as merch_no, m.sangho as sangho, m.saup_no as saup_no,
m.merch_rank as merch_rank, m.ceo as ceo, m.regi_date as regi_date, m.bl_cd as bl_cd,
sum(decode(tran_cd,'M1',nvl(auth_amt,0),'U1',nvl(auth_amt,0),'H1',nvl(auth_amt,0),0))-sum(decode(tran_cd,'M2',nvl(auth_amt,0),'H2',nvl(auth_amt,0),0)) as save_amt,
sum(decode(tran_cd,'M1',1,'U1',1,0)) as save_cnt,
sum(decode(tran_cd,'M1',nvl(save_pnt,0),0)) as save_pnt,
sum(decode(tran_cd,'M3',1,0)) as use_cnt,
sum(decode(tran_cd,'M3',nvl(save_pnt,0),0)) as use_pnt,
sum(decode(tran_cd,'M1',nvl(save_pnt,0),0)) - sum(decode(tran_cd,'M2',nvl(save_pnt,0),0))
- sum(decode(tran_cd,'M3',nvl(save_pnt,0),0)) + sum(decode(tran_cd,'M4',nvl(save_pnt,0),0))
- sum(decode(tran_cd,'U3',nvl(save_pnt,0),0)) - sum(decode(tran_cd,'M9',nvl(save_pnt,0),0))
+ sum(decode(tran_cd,'M1',decode(status_cd,'00',nvl(add_save_pnt,0),0), 'H1',decode(status_cd,'00',nvl(add_save_pnt,0),0),0)) as remain_pnt
from jehyu_master j, merch_master m, tlf_point_810 t
WHERE m.merch_no = t.merch_no(+) AND j.jehyu_cd = m.jehyu_cd
group by j.jehyu_cd, j.jehyu_name, m.merch_no, m.sangho, m.saup_no, m.merch_rank, m.ceo, m.regi_date, m.bl_cd
이렇게 실행을 하면 810 제휴사 가맹점의 포인트 상황이 나와야되는데 810외의 다른 제휴사들도 나옵니다
어어떻게 해야되나요........
|