Re: Minus in View?

Andreas Mosmann wrote:
> xiaoluma_at_gmail.com schrieb am 28.09.2005 in
> <1127943133.340309.182260_at_o13g2000cwo.googlegroups.com>:
>
> > Hey, there,
> > I was trying to create a view to get the new customer# fro year 2005
> > using the following code:
> > CREATE OR REPLACE VIEW NewCustomers
> > AS
> > SELECT Customer# FROM tblOrderFact
> > WHERE TransDate BETWEEN '20050101' AND '20051231'
> > MINUS
> > SELECT Customer# FROM tblOrderFact
> > WHERE TransDate < '20050101'
> Did I miss anything? In that special case, why don't you use only
> CREATE OR REPLACE VIEW NewCustomers
> AS
> SELECT Customer# FROM tblOrderFact
> WHERE TransDate BETWEEN '20050101' AND '20051231'
> should have the same result, isn't it?
The table tblOrderFact is an accumulate table. Say, Customer A shopped in year 2004, and came back to shop in 2005. In the table, I will have tow orders associated to this customer, one in 2004 and one in 2005. The purpose of my view is to get NEW customers, in other words, the customers who had never made any order before year 2005.

>
> > The error message told me that MINUS was not allowed in updateble
> > views. Can anyone give me suggestions?
> I used MINUS in views w/o errors. I do not know
> for example:
>
> create view XYZ as
> SELECT E.cid, e.ctimestamp FROM tberrorlog E
> WHERE e.ctimestamp BETWEEN To_date('08/01/2005') AND
> To_date('09-30-2005')
> MINUS
> SELECT E.cid, e.ctimestamp FROM tberrorlog E
> WHERE e.ctimestamp < To_date('08-01-2005')
>

I am using ORACLE 8.1, do you think that might cause the error? Thanks.

> > Thanks.
> > Xiaolu
> hth
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de
Received on Thu Sep 29 2005 - 15:14:19 CDT