Home » SQL & PL/SQL » SQL & PL/SQL » View, Not Exists, ... H E L P Please...
View, Not Exists, ... H E L P Please... [message #18595] Thu, 07 February 2002 01:48 Go to next message
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..
Re: View, Not Exists, ... H E L P Please... [message #18598 is a reply to message #18595] Thu, 07 February 2002 04:13 Go to previous messageGo to next message
INTROV
Messages: 20
Registered: February 2002
Junior Member
Check your view name for payment_all in the query. It should be pay_all as per your message.
Re: View, Not Exists, ... H E L P Please... [message #18629 is a reply to message #18598] Thu, 07 February 2002 16:41 Go to previous message
diaz
Messages: 58
Registered: October 2001
Member
the real problem is not on error like that..
the real problem is .. i have to make some query here..

thanks anyway
Previous Topic: Re: Regarding Storage Of Images Into Database
Next Topic: One question about SQL
Goto Forum:
  


Current Time: Thu Mar 28 06:31:52 CDT 2024