Minus in View?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xiaoluma@gmail.com - 28 Sep 2005 22:32 GMT

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'

The error message told me that MINUS was not allowed in updateble
views. Can anyone give me suggestions?
Thanks.
Xiaolu

Reply to this Message

Andreas Mosmann - 29 Sep 2005 06:04 GMT

xiaoluma@gmail.com schrieb am 28.09.2005 in
<1127943133.340309.182260@o13g2000cwo.googlegroups.com>:

> Hey, there,
> I was trying to create a view to get the new customer# fro year 2005
[quoted text clipped - 6 lines]
> 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 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')

> Thanks.
> Xiaolu

hth
Andreas

Signature

wenn email, dann AndreasMosmann <bei> web <punkt> de

Reply to this Message

xiaoluma@gmail.com - 29 Sep 2005 21:14 GMT

> xiaoluma@gmail.com schrieb am 28.09.2005 in
> <1127943133.340309.182260@o13g2000cwo.googlegroups.com>:
[quoted text clipped - 15 lines]
> 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?
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Andreas Mosmann - 30 Sep 2005 09:04 GMT

xiaoluma@gmail.com schrieb am 29.09.2005 in
<1128024859.920653.291740@o13g2000cwo.googlegroups.com>:

.

>>> CREATE OR REPLACE VIEW NewCustomers
>>> AS
[quoted text clipped - 3 lines]
>>> 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
[quoted text clipped - 7 lines]
> 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

Signature

wenn email, dann AndreasMosmann <bei> web <punkt> de

Reply to this Message