Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Minus in View?
xiaoluma_at_gmail.com schrieb am 29.09.2005 in
<1128024859.920653.291740_at_o13g2000cwo.googlegroups.com>:
.
>>> 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.If so I would assume the query above doesn't give you the result you expect. Did you really try this query?
>>> The error message told me that MINUS was not allowed in updateble
>>> views. Can anyone give me suggestions?
.
> I am using ORACLE 8.1, do you think that might cause the error?
> Thanks.
Maybe, I never worked on ORACLE 8.1
Andreas
-- wenn email, dann AndreasMosmann <bei> web <punkt> deReceived on Fri Sep 30 2005 - 03:04:34 CDT