Re: SQL query of a tree structure
- From: Vitalis Jerome <vitalisman@xxxxxxxxx>
- To: marc.demlenne@xxxxxxxxx
- Date: Thu, 28 Apr 2005 11:19:03 +0200
On 4/27/05, Marc Demlenne <marc.demlenne@xxxxxxxxx> wrote:
> Hi all,
>=20
> I'm searching help to solve the following problem :
>=20
> I have to query an ORACLE db to get a tree structure which is stored insi=
de=3D
> .
> The DB already exists, and i'm not able to modify its structure. I
> could only add supplementary columns if necessary.
>=20
> The trees (which represents a decisional tree) is stored in multiple
> tables. We have one for root, and one table by type of decision
> criterions or by leaf. On each record of each tree table, we have in
> addition to usefull parameters proper to each criterion, 4 "tree
> specific" ones which gives respectively the TYPE and NODEID of
> previous and next node in the tree.
> The 'custom columns' can vary a lot for one criterion to another, but
> the 'tree specific' ones are always the same, except for root'
>=20
> So, eg :
>=20
> Root :
> CUSTOM INFOS - NXT_TYPE - NXT_NODEID
> azezerert - 12 - 2
> azefsdfsdf - 15 - 1
>=20
> For criterion 1 (criterion type =3D3D 12) :
> CUSTOM INFOS - NODE_ID - NXT_TYPE - NXT_ID - PREV_TYPE - PREV_I=
D
> blabla - 1 - ...
> sdfsf df - 2 - 18 - 2 - 1 - 1
> sddffsf df - 2 - 18 - 3 - 1 - 1
> blabla - 3 - ...
>=20
> For criterion 2 (criterion type =3D3D 15) :
> CUSTOM INFOS - NODE_ID - NXT_TYPE - NXT_ID - PREV_TYPE - PREV_I=
D
> sdfsf df - 1 - 25 - 17 - 1 - 2
>=20
> ...
>=20
> So I'm trying to get in SQL a simple way to get this structure, in
> order to display it on a GUI without having to query the tree as many
> times as criterions it contains ...
>=20
> If someone could help, it would be very appreciated ...
>=20
> Thanks a lot,=3D20
Hi,
I think that if you want to use only simple SQL to get the whole
structure, you'll have to create a common table for all leaves from
your existing tables, without the "custom infos" and with an
additional column for the criterion type, and then write a query that
use:
- a hierarchical clause (CONNECT BY) to scan the new table
- a CASE expression to select the "custom infos" in the right table
according to the criterion type of the leaf
Regards,
Jerome
--
//www.freelists.org/webpage/oracle-l
Other related posts:
