I’m having a problem denormalizing a relationship table.
parentid childid lvl isleaf
1 1 1 N
1 10 2 N
10 100 3 N
10 101 3 N
100 200 4 Y
101 201 4 Y
The output I am trying to produce is:
leafid lvl1_id lvl2_id lvl3_id lvl4_id
200 1 10 100 null
201 1 10 101 null
When the level is equal to the level of the leaf, the value should be null.
My basic concept is to use a case statement to denormalize the levels (like this)
MAX(CASE WHEN lvl = 1 AND isleaf = 'N' THEN childid ELSE null END) as lvl1_id, MAX(CASE WHEN lvl = 2 AND isleaf = 'N' THEN childid ELSE null END) as lvl2_id ...
But I am having trouble figuring out the relationship to the leaf node.
from hier_table leaf left outer join hier_table node on leaf.parentid = node.childid
but the only level populated is the one before the leaf.
This is in Netezza, which does not yet support recursive queries.
Any help would be greatly appreciated. I am trying to avoid having to do n number of joins to traverse the levels from the leaf up, and am able to create a temp table based on the relationship table if that helps the process.