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@perceptron.com