How to gather hierarchical data items and build a tree considering their dependencies

Question

There is a table containing hierarchical data, e.g.:

| table "attribute_instances"                           |
+----+----------+------------+---------------+----------+
| id | tree_ref | parent_ref | attribute_ref | data_ref |
+----+----------+------------+---------------+----------+
|  1 |        1 |         -1 |             1 |        1 |
|  2 |        1 |          1 |             2 |        2 |
|  3 |        2 |         -1 |             1 |        3 |
|  4 |        2 |          3 |             2 |        2 |

It contains many separate trees (see tree_ref), each of them instantiating some attributes (see attribute_ref) and have a data reference data_reference, where data might be referenced in other trees, too.

Now, those trees should be merged into a single tree, in which (by now) up to 5 attributes may be chosen as level for that tree, e.g.:

attribute => level
------------------
        2 =>     1
        1 =>     2

What I need is one or more queries, that collects the data from table attribute_instances and gives a result as follows:

| table "merged_attribute_instances"         |
+----+------------+---------------+----------+
| id | parent_ref | attribute_ref | data_ref |
|  5 |         -1 |             2 |        2 |
|  6 |          5 |             1 |        1 |
|  7 |          5 |             1 |        3 |

This is the desired merged tree:

id:5 - data_ref:2
  id:6 - data_ref:1
  id:7 - data_ref:3

Note, that attribute_ref = 2 occurs only once in the resulting tree, as all instances of it have same data_ref value (that is 2).

I've tried some joins like

select *
  from attribute_instances a
  join attribute_instances b on a.tree_ref = b.tree_ref

But that seems to me being bad for having user-defined tree depth. I'm sure there is a better solution.

UPDATE: I should add, that table merged_attribute_instances is a temporary table. And the collecting query is iterated with for..do. In the loop the collected attribute_instances are then added to the temporary table.

Answer 1

OK, then use this:

SET TERM ^ ;

create or alter procedure GETTREENODES
returns (
    ID integer,
    TREE_REF integer,
    PARENT_REF integer,
    ATTRIBUTE_REF integer,
    DATA_REF integer)
as
declare variable DATAREFEXISTS varchar(4096);
begin
 DATAREFEXISTS = ',';
 for
    Select id, tree_ref, parent_ref, attribute_ref, data_ref from attribute_instances
     into :id, :tree_ref, :parent_ref, :attribute_ref, :data_ref
 do begin
    IF (position(',' || data_ref || ',', DATAREFEXISTS) =0) THEN
    begin
      suspend;
      DATAREFEXISTS = DATAREFEXISTS || data_ref || ',' ;
    end
 end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON ATTRIBUTE_INSTANCES TO PROCEDURE GETTREENODES;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE GETTREENODES TO SYSDBA;

Call it like this:

Select * from gettreenodes
order by tree_ref, parent_ref