View, Not Exists, ... H E L P Please... [message #18595] |
Thu, 07 February 2002 01:48 |
diaz
Messages: 58 Registered: October 2001
|
Member |
|
|
hello..
i have this tables :
bill01, bill02, bill03,.. bill11
payment01,..,payment11
all of bill have the same fields : msisdn,eop,cost,paydate
all of payment have the same fields :
msisdn,paydate,sumpayment
each of them represent all regional
i manage them like that because i thought will be easier to have queries per region
because lots of the working is done per region
i have build view for bill :
bill_all like this :
select msisdn,eop,cost,paydate, region from bill01,regional where code_reg='01'
union all
select msisdn,eop,cost,paydate, region from bill02,regional where code_reg='02'
...
until 11
and for payment :
pay_all
select msisdn, paydate, sumpayment, region
from payment01, regional where kode_reg='01'
union all
select msisdn, paydate, sumpayment, region
from payment02, regional where kode_reg='02'
...
until 11
now the problem is .. i have to build query that requires all payment that doesn't exist in apropriate bill. the meaning of apropriate is has the same eop (periode of billing-payment) and the customer (identified by msisdn) is registered to one region
so.. if the customer pays but insert the wrong msisdn (which might appear on another region) it should be caught and informed as non-valid payment..
but if i just query like this :
select msisdn,paydate,sumpayment from payment_all
where not exists
(select * from bill_all where eop='200110' and msisdn=payment_all.msisdn)
and to_char(tglbayar,'YYYYMM')='200110';
it'll not informed that the msisdn is non-valid payment because it exist on the bill_all view
i do have one table : regional with fields : code_reg and the name of the region..
but i still can't figure the query..
and i'm asking for your help..
please help me..
|
|
|
|
|