Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01437 cannot have join with CONNECT BY

Re: ORA-01437 cannot have join with CONNECT BY

From: BobH <b-horton_at_worldnet.att.net>
Date: Tue, 21 Apr 1998 19:37:37 -0400
Message-ID: <6hjaln$3up@bgtnsc03.worldnet.att.net>


the columns in the CONNECT Clause need to be FK, PK i.e have a relationship. your OPerations Consumptions table's PK looks to be OPERATION_CODE Pepe Lima wrote:
>
> 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 - 18:37:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US