Re: Minus in View?


Andreas Mosmann wrote:
> xiaoluma@xxxxxxxxx schrieb am 28.09.2005 in
> <1127943133.340309.182260@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>:
>
> > 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

.

Relevant Pages

  • Re: Using xp coa of dead pc on new pc
    ... As a former DBA / Data Analyst, ... a 50-number (plus or minus) key isn't needed, and the customer should ... bow down toward Redmond, ...
    (alt.sys.pc-clone.dell)
  • Re: Using xp coa of dead pc on new pc
    ... Journey wrote: ... a 50-number (plus or minus) key isn't needed, and the customer should ... encoding information within a key is ...
    (alt.sys.pc-clone.dell)
  • Re: Few, Fewer, Fewest
    ... I once had a big to-do with a representative of our customer ... minus 500 volts was greater than minus 400. ... < -400 V, the reference point is assumed in that notation, and -500 V ...
    (alt.usage.english)
  • Minus in View?
    ... I was trying to create a view to get the new customer# fro year 2005 ... SELECT Customer# FROM tblOrderFact ... WHERE TransDate BETWEEN '20050101' AND '20051231' ... The error message told me that MINUS was not allowed in updateble ...
    (comp.databases.oracle.misc)
  • Re: Minus in View?
    ... SELECT Customer# FROM tblOrderFact ... WHERE TransDate BETWEEN '20050101' AND '20051231' MINUS ... In the table, I will have tow orders associated to this customer, one in 2004 and one in 2005. ...
    (comp.databases.oracle.misc)