Parsing order (Was: "select ... connect by..." in the view)

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 10 Mar 2004 09:02:29 -0500
Message-ID: <003101c406a8$53923660$0704a8c0@development.perceptron.com>

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

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 10 2004 - 08:07:57 CST