Skip to Content

Problem statement

Flattening of hierarchy solution that i found on SCN either required modelling or use of MDX client on a Attribute View with hierarchy.

Somehow there seemed a way for a simpler low tech solution. So here is my attempt at attacking the problem with only SQL and SQL Script.

Data looks like this

aField

bField

1
2 1
3 1
4 2
5 2
6 2
7 3
8 3
9 3

the output needs to look like this (displaying onlyaField values) :

Level 1 Level 2 Level 3
1 2 4
3 5
6
7
8
9

Solution Explained

The solution has two parts
  1. Tag each row with the ‘level’ it is on – we will use a User Defined Table function for this
  2. Use the level to ‘pivot’ the table on its side – We will use SQL Query with table views
Part 1: Tagging each row with level
  1. Identify the rows whose ‘aField’ value never occurs in ‘bField’ (for e.g. values 4 to 9 never occur in bField)
  2. Row identified in step 1 are tagged with a  level (for e.g. 1)
  3. Once tagged, remove the rows from the reckoning (for e.g. remove rows where aField value between 4 to 9, leaving rows with values 1..3 in aField)
  4. loop over steps 1 to 3 until bField has only null values remaining. (for e.g. row 1)
  5. Union all tagged rows and return

Output

AFIELD BFIELD AFIELD AFIELD BFIELD LEVEL AFIELD BFIELD
1 ? STEP 1-> STEP 2-> STEP 3-> 1 ?
2 1 2 1
3 1 3 1
4 2 4 4 2 1
5 2 5 5 2 1
6 2 6 6 2 1
7 3 7 7 3 1
8 3 8 8 3 1
9 3 9 9 3 1
Part 2: Use the level to ‘pivot’ the table on its side
  1. We generate on (ad hoc) view per level column in the output filtered on level
  2. Each view also uses a window function to generate rownumber
  3. Using row numbers write an outer join to generate the output

Output:

LEVEL1 LEVEL2 LEVEL3
1 2 4
? 3 5
? ? 6
? ? 7
? ? 8
? ? 9

The code:


FUNCTION "************"."***********.rootpkg.procs.dml::connectby" ( )
  RETURNS TABLE(aField tinyint,bfield tinyint, level tinyint)
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER AS
BEGIN
  DECLARE Rank tinyint :=0;
  DECLARE level tinyint :=1;
  DECLARE I Tinyint;
-- get the data to process
  bothFields = select aField,bField from  "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."FLATSTRUCT";
--create the output stucture
  outputTable = select aField,bField, :level as level from :bothFields where 1=2;
  -- check if we need to an iteration
  select count(*)
  into Rank
  from (select distinct bField from :bothFields where bField is not null)
  where bField is not null;
  --get distinct superior field and count of valid values
  while :Rank > 0 Do
  -- Get the lowest level - not in superior field and tag with level id
  lowestlevel = Select aField, :level as level
  from(select aField from :bothFields
    EXCEPT
    select distinct bField from :bothFields);
  -- get rows that were not in the lowest level
  newAField = select aField from :bothFields
  EXCEPT
  select aField from :lowestlevel;
  --add tagged levels to output
  tempOut=CE_JOIN(:lowestlevel,:bothFields,["AFIELD"],["AFIELD","BFIELD","LEVEL"]);
  outputTable = Select aField, bField,  :level as level from :tempOut
    UNION ALL
    Select aField, bField, level from :outputTable;
  --remove the rows tagged with level id from data set
  bothFields = CE_JOIN(:newAField,:bothFields,["AFIELD"],["AFIELD","BFIELD"]);
  --reset level id
  level := :level+1;
  -- check if we need to do another iteration
  select count(*)
  into Rank
  from (select distinct bField from :bothFields where bField is not null)
  where bField is not null;
  END WHILE;
  --attaching the top most level
  outputTable = Select aField, bField, :level as level from :bothFields WHERE bField is null
    UNION ALL
    Select aField, bField, level from :outputTable;
  return select * from :outputTable;
END;





The SQL Query


select three.afield  as level1, two.AFIELD as level2, one.AFIELD as level3
from
(select afield, ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =1) one
full outer join
(select afield , ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =2) two
on one.rnum = two.rnum
full outer join
(select afield , ROW_NUMBER() over (order by afield) as rnum from "DEV_1X28Q7TC9RQSNZH49YABNQ4JZ"."i036632sapdev.rootpkg.procs.dml::connectby"()
Where level =3) three
on one.rnum = three.rnum



Similar Solutions:

Please have a look at similar solutions Flattening the hierarchy using SQL code

Request:

This is my first blog post, please do leave feedback in the comments

Edit:

Changed the heading as it did not really reflect what this blog post actually does

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Anil K

    Hello Aditya,

    Nicely explained by breakingup the logic into sub-parts.

    Q1) Have completed Function creation similar to above.

    similar to above. Create Function executed fine.

    Next, when trying to execute select statement : (SQL Statement mentioned below), it gives below error:

    Any Idea?

    Error:

    Could not execute ‘select three.”aField” as level1, two.”aField” as level2, one.”aField” as level3 from (select …’ in 6.027 seconds .

    SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;CalculationNode ($$_SYS_SS2_RETURN_VAR_$$) -> operation (CustomLOp):Compilation failed; CompileServerNotFoundException: No details;

    Q2 ) Does this logic mentioned above working for Dynamic scenario wherein the hierarchy levels keep changing (let say: for Cost Center hierarchy the levels can be 10 and for a different hierarchy: Sales Hierarchy the levels can be 15).

    Does it work for all these scenarios?

    SELECT Statement for above SQL error:

    =============================

    select three.”aField”  as level1, two.”aField” as level2, one.”aField” as level3

    from

    (select “aField”, ROW_NUMBER() over (order by “aField”) as rnum from “EDU”.”HIER_FLAT”()

    Where level =1) one

    full outer join

    (select “aField” , ROW_NUMBER() over (order by “aField”) as rnum from “EDU”.”HIER_FLAT”()

    Where level =2) two

    on one.rnum = two.rnum

    full outer join

    (select “aField” , ROW_NUMBER() over (order by “aField”) as rnum from “EDU”.”HIER_FLAT”()

    Where level =3) three

    on one.rnum = three.rnum

    Thanks,

    AK

    (0) 

Leave a Reply