From oracle-l-bounce@freelists.org Thu Feb 26 10:48:17 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1QGmH503981 for ; Thu, 26 Feb 2004 10:48:17 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1QGmGo03973 for ; Thu, 26 Feb 2004 10:48:16 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7299F397C94; Thu, 26 Feb 2004 11:46:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 26 Feb 2004 11:45:12 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from ihub.perceptron.com (ihub.perceptron.com [68.22.13.201]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 17270397D14 for ; Thu, 26 Feb 2004 11:41:24 -0500 (EST) Received: from [192.168.4.7] (ineyman@perceptron.com) by Office-Logic InterChange; Thu, 26 Feb 2004 11:45:45 -0500 From: "Igor Neyman" To: Subject: "select ... connect by..." in the view Date: Thu, 26 Feb 2004 11:44:16 -0500 Message-ID: <005001c3fc87$c60e3430$0704a8c0@development.perceptron.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0051_01C3FC5D.DD382C30" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-archive-position: 2105 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ineyman@perceptron.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------=_NextPart_000_0051_01C3FC5D.DD382C30 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0051_01C3FC5D.DD382C30 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

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 =3D = …

 

Igor Neyman, OCP DBA

ineyman@perceptron.com

 

 

------=_NextPart_000_0051_01C3FC5D.DD382C30-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------