| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parsing order (Was: "select ... connect by..." in the view)
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.
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_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman
Sent: Thursday, February 26, 2004 11:44 AM
To: oracle-l_at_freelists.org
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_at_perceptron.com
 |
 |