0

I have a problem with creating tree with my entities.

There are entities with entity id which are connected with other entities by parent_ent_id.

My input is

ent_id ent_name parent_ent_id
1 GSM_FACTORY NULL
2 Maszyna_1 1
5 Maszyna_2 1
7 GSM_FACTORY_2 NULL
11 Maszyna_3 7
12 Maszyna_4 7

And with my code

WITH genealogy AS 
(
    SELECT 
        ent_id, ent_name,
        parent_ent_id,
        can_track_oee,
        0 AS generation_number,
        0 as par,
        CAST('0' AS varchar(50)) AS dens,
        CAST('0_' + CAST(ent_id AS varchar(50)) AS varchar(50)) AS order_sequence   
    FROM 
        ent
    WHERE 
        parent_ent_id IS NULL
 
    UNION ALL
 
    SELECT 
        child.ent_id, child.ent_name,
        child.parent_ent_id,
        child.can_track_oee,
        generation_number + 1 AS generation_number,
        par + 1,
        CAST(dens + '_' +
            CAST(
                DENSE_RANK() OVER (ORDER BY child.parent_ent_id)
            AS VARCHAR(50)
        ) AS VARCHAR(50)) AS dens,

        CAST(order_sequence + '_' + CAST(child.ent_id AS VARCHAR (50)) AS VARCHAR(50)) AS order_sequence
    FROM 
        ent child
    JOIN 
        genealogy g ON g.ent_id = child.parent_ent_id
)
SELECT * 
FROM genealogy

I'm getting something like this as a result:

ent_id ent_name parent_ent_id can_track_oee generation_number par dens order_sequence
1 GSM_FACTORY NULL 0 0 0 0 0_1
7 GSM_FACTORY_2 NULL 0 0 0 0 0_7
11 Maszyna_3 7 1 1 1 0_1 0_7_11
12 Maszyna_4 7 1 1 1 0_1 0_7_12
2 Maszyna_1 1 1 1 1 0_1 0_1_2
5 Maszyna_2 1 1 1 1 0_1 0_1_5

Why do I have dens like 0_1 0_1 0_1 0_1 and not like 0_1 0_1 0_2 0_2

Charlieface
  • 52,284
  • 6
  • 19
  • 43
koxermaxerelo
  • 1
  • 1
  • The problem is that the `DENSE_RANK` is only calculated per each row output from the preceding recursion level, not over the whole set. This is documented here https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16#guidelines-for-defining-and-using-recursive-common-table-expressions. Also I don't get your results exactly, see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3ed374183142f7d4613307204e135e24, and `can_track_oee` is missing from your sample data (it seems irrelevant so I rmeoved it from the query) – Charlieface May 25 '22 at 12:26
  • What you *can* do, is assign on the first level a row-number, then for each set of child rows *per row of the previous set* assign a new row-number. See https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c436c17cf80a5d4bc3bfcc4c4037b038. You cannot assign a row-number across different sets of recursion, as the recursion works row-by-row, not in sets. – Charlieface May 25 '22 at 12:46

0 Answers0