ORA-01437 cannot have join with CONNECT BY

From: Pepe Lima <lima.ensa_at_pnt.servicom.es>
Date: Tue, 21 Apr 1998 11:21:04 +0200
Message-ID: <6hhoaf$3fp@wendy.mad.servicom.es>

Hi,

I have two tables with this structure (simplified):

Operations                            Operations_Consumptions
------------------------              ---------------------------------
Operation_Number               Operation_Number
Operation_Code                   Consumption_Material
Valid_From                            Production_Material
Valid_To                                Quantity

Description

In the operations table I have for the same operation_code differents operation numbers depending on the validation dates to record its evolution:

op_code from date to date consumption production quantity ----------- ------------- ------------- --------------- ------------- ------

---

C01215  23-Mar-98 31-Mar-98    M1111          M1112       0.015
C01215  01-Apr-98                        M1111          M1112       0.018

....

Also, I've created a view:

Create View Consumptions As
Select Cons.Operation_Number, Consumption_Material, Production_Material, Quantity, Valid_From, Valid_To
From Operations Op, Operations_Consumptions Cons Where Op.Operation_Number = Cons.Operation_Number;

Then, appears 'ORA-01437 cannot have join with CONNECT BY' executing this query:

Select Level, Consumption_Material, Quantity From Consumptions
Start With Production_Material = 'M1112' And Valid_From >= Sysdate And (Valid_To <= Sysdate Or Valid_To Is Null) Connect By Prior Consumption_Material = Production_Material And Valid_From >= Sysdate And (Valid_To <= Sysdate Or Valid_To Is Null);

This is because the view has a union in it, but I think that is needed.

Any idea?

Thanks.

Pepe Lima
Estampaciones Noroeste, S.A.
Vigo - Spain
e-mail: lima.ensa_at_pnt.servicom.es Received on Tue Apr 21 1998 - 04:21:04 CDT