Informix Siblings - Table with repeated Values

Question

I try to get a result like this:

10 Pos 1 = 20

---------------Pos 1

---------------Pos 2 = 50

---------------------------Pos 1
Pos 2 = 30

Pos 3 = 40

---------------Pos 1

---------------Pos 2

CREATE TABLE beispiel (
    fa INTEGER,
    fa_pos integer,
    verurs INTEGER,
    verurs_pos INTEGER,
    auftrag INTEGER
);

INSERT INTO beispiel VALUES (   10  ,   1   ,   24548   ,   1   ,   24548   );
INSERT INTO beispiel VALUES (   10  ,   2   ,   24548   ,   1   ,   24548   );
INSERT INTO beispiel VALUES (   10  ,   3   ,   24548   ,   1   ,   24548   );
INSERT INTO beispiel VALUES (   20  ,   1   ,   10  ,   1   ,   24548   );
INSERT INTO beispiel VALUES (   20  ,   2   ,   10  ,   1   ,   24548   );
INSERT INTO beispiel VALUES (   30  ,   0   ,   10  ,   2   ,   24548   );
INSERT INTO beispiel VALUES (   40  ,   1   ,   10  ,   3   ,   24548   );
INSERT INTO beispiel VALUES (   40  ,   2   ,   10  ,   3   ,   24548   );
INSERT INTO beispiel VALUES (   50  ,   1   ,   20  ,   2   ,   24548   );

I run this statement:

SELECT 
LEVEL, sys_connect_by_path(fa, "/") AS pfad, 
fa, fa_pos, verurs, verurs_pos

FROM beispiel

CONNECT BY PRIOR fa = verurs

GROUP BY LEVEL,fa, pfad, fa_pos, verurs, verurs_pos
ORDER siblings BY verurs

Result:

   level    pfad    fa  fa_pos  verurs  verurs_pos
1   /10 10  1   24548   1
2   /10/20  20  2   10  1
2   /10/20  20  1   10  1
3   /10/20/50   50  1   20  2
2   /10/30  30  0   10  2
1   /40 40  1   10  3
1   /50 50  1   20  2
1   /20 20  1   10  1
1   /30 30  0   10  2
2   /10/40  40  1   10  3
1   /10 10  3   24548   1
1   /10 10  2   24548   1
2   /10/40  40  2   10  3
2   /20/50  50  1   20  2
1   /20 20  2   10  1
1   /40 40  2   10  3

Why do I get as result /50? How can I ignore this value? And I get duplicated values and the Tree doesn´t look like a Child --> Parent --> Grandparent Structure.

Do I really need to do a GROUP BY?

Any idea?

I need to get an ordered result with siblings.

Answer 1

You can modify the CONNECT BY clause and use the CONNECT_BY_ROOT function in your SQL query. Additionally, you can use the ORDER SIBLINGS BY clause to order the result by specific columns. Here's the modified SQL query:

SELECT 
  LEVEL,
  SYS_CONNECT_BY_PATH(fa, "/") AS pfad,
  CONNECT_BY_ROOT(fa) AS root_fa,
  CONNECT_BY_ROOT(fa_pos) AS root_fa_pos,
  fa, 
  fa_pos, 
  verurs, 
  verurs_pos
FROM beispiel
CONNECT BY PRIOR fa = verurs AND PRIOR auftrag = auftrag
ORDER SIBLINGS BY verurs_pos, fa_pos;

Add CONNECT_BY_ROOT(fa) AS root_fa and CONNECT_BY_ROOT(fa_pos) AS root_fa_pos to capture the root values for fa and fa_pos for each branch in the hierarchical structure.

The CONNECT BY clause is modified to include AND PRIOR auftrag = auftrag to ensure that the hierarchy is connected based on the auftrag column as well. This way, each branch will only contain values related to the same auftrag.

Use the ORDER SIBLINGS BY clause to specify the order of siblings in the hierarchy. In this case, we order them first by verurs_pos and then by fa_pos.

With these modifications, you should get the desired ordered result without duplicates, and the tree structure will represent the Child -> Parent -> Grandparent relationship based on fa and verurs columns within the same auftrag.

Answer 2

Finally I get it, but my code looks a little bit "crazy". Can I do all these steps in one SELECT?

SELECT *
FROM (
    SELECT LEVEL
        ,CONNECT_BY_ISLEAF leaf
        ,CONNECT_BY_ISCYCLE cycle
        ,CONNECT_BY_ROOT(fa) AS root_fa
        ,CONNECT_BY_ROOT verurs AS verurs_top
        ,SYS_CONNECT_BY_PATH(fa, "/") AS pfad
        ,*
    FROM (
        SELECT fa || "-" || fa_pos AS fa_ID
            ,verurs || "-" || verurs_pos AS verurs_ID
            ,*
        FROM beispiel
        ORDER BY verurs_id
            ,fa_id ASC
        ) connect BY nocycle PRIOR fa_id = verurs_id
    ORDER siblings BY fa_id
        ,verurs ASC
    )
WHERE root_fa = (
        SELECT min(fa)
        FROM beispiel
        )