Re: Parsing order (Was: "select ... connect by..." in the view)
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Mar 2004 10:44:17 -0400
I don't know, but maybe this is a bug, I can't use function tables PIPED, I use
function that returns a cursor
and works fine.
----- Original Message -----
From: Igor Neyman
To: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, March 10, 2004 10:02 AM
Subject: Parsing order (Was: "select ... connect by..." in the view)
Ok, I see no takers on my original question (how to create a view based on
"select . connect by. and prior. start with." to get the hierarchy).
May be this will spark some interest, I guess it's about parsing order.
The view I'm using looks like this:
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 10 08:42:51 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create or replace view test_view_ms as
2 SELECT
3 vt.model_id, vt.lvl "LEVEL", vt.parent_subassembly_id, vt.subassembly_id
4 FROM test_ms tm, TABLE(m_tree(tm.model_id)) vt;
View created.
And it works fine; "m_tree" is a function that hides "select . connect by.
and prior. start with.".
"test_ms" table added to the join in order to be able to specify "model_id"
when querying this view.
But, if I change the order in the "FROM" list, I get an error:
SQL> create or replace view test_view_ms as
2 SELECT
3 vt.model_id, vt.lvl "LEVEL", vt.parent_subassembly_id, vt.subassembly_id
4 FROM TABLE(m_tree(tm.model_id)) vt, test_ms tm;
FROM TABLE(m_tree(tm.model_id)) vt, test_ms tm
*
ERROR at line 4:
ORA-00904: "TM"."MODEL_ID": invalid identifier
Is it because of the way (order) Oracle is parsing this view?
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Igor Neyman
Sent: Thursday, February 26, 2004 11:44 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: "select ... connect by..." in the view
Just wonder if anyone has (or came across) better solution for this.
I had a request from developer to put "select . connect by." into view.
He wants to get the hierarchy in one step, but the "framework" he is using
doesn't allow him to issue "select . connect by.".
I've got rather ugly solution for him (which I can share if anyone
interested, didn't want this message to be long), using user types, function,
and the final view looks like this:
create or replace view test_view_ms as
SELECT
vt.model_id, vt.lvl "LEVEL", vt.parent_subassembly_id, vt.subassembly_id
FROM test_ms tm, TABLE(m_tree(tm.model_id)) vt;
So, now he can get the hierarchy for specified model with just:
select from test_view_ms where model_id = .
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
Other related posts:
