How to populate runtime field value into another runtime field using CDS view.

[nagaraosunkara]

Hello All,

We have a requirement, where we had to flatten the hierarchy maintained by BW team.

Consider Segment in BW has 17 levels, we need to create a CDS to flatten the hierarchy and show the values using CDS. CDS has been built for the same, and we see its working as expected.

We have a new requirement that, if hierarchy ends at level 2, same value should be displayed from level 3 to level 17, without leaving them blank. eg: if segment 100 ends at level 2, value 100 should be displayed from level 3 to level 17.

We had looked at multiple options and implemented it with the following approach (keeping the code that we are using for level 8 (we check if level 7 has data, if yes we copy 7 data to 8, else check level 6 and copy it to level 7 and 8, and so on): 

case when ( AccountL8 is initial and
              AccountL7 is not initial ) then AccountL7
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is not initial ) then AccountL6
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is initial and
              AccountL5 is not initial ) then AccountL5
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is initial and
              AccountL5 is initial and
              AccountL4 is not initial ) then AccountL4
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is initial and
              AccountL5 is initial and
              AccountL4 is initial and
              AccountL3 is not initial ) then AccountL3
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is initial and
              AccountL5 is initial and
              AccountL4 is initial and
              AccountL3 is initial and
              AccountL2 is not initial ) then AccountL2
       when ( AccountL8 is initial and
              AccountL7 is initial and
              AccountL6 is initial and
              AccountL5 is initial and
              AccountL4 is initial and
              AccountL3 is initial and
              AccountL2 is initial and
              AccountL1 is not initial ) then AccountL1
       else AccountL8 end as AccountL8, 

We see that is kind of crude way to do.. Any pointers to optimize this would be helpful

[umasaral]

To optimize your CDS logic for flattening the hierarchy, consider using a more concise approach with a recursive Common Table Expression (CTE).if your environment supports it. Here’s a streamlined version:
1.Use a Recursive CTE: Define a CTE that iterates through the levels, propagating values from higher levels down to lower levels.
2.Simplified CASE Logic: Replace the extensive CASE statements with a loop or recursive function to fill in the values dynamically.
Here's a logic in pseudocode:

/*sql*/
WITH RECURSIVE Hierarchy AS (
SELECT
AccountL1, AccountL2, AccountL3, AccountL4, AccountL5,
AccountL6, AccountL7, AccountL8
FROM YourTable
UNION ALL
SELECT
AccountL1,
COALESCE(AccountL2, AccountL1) AS AccountL2,
COALESCE(AccountL3, AccountL2) AS AccountL3,
COALESCE(AccountL4, AccountL3) AS AccountL4,
COALESCE(AccountL5, AccountL4) AS AccountL5,
COALESCE(AccountL6, AccountL5) AS AccountL6,
COALESCE(AccountL7, AccountL6) AS AccountL7,
COALESCE(AccountL8, AccountL7) AS AccountL8
FROM Hierarchy
WHERE NOT (AccountL8 IS NOT NULL)
)
SELECT * FROM Hierarchy;

Key Benefits:
Efficiency: Reduces repetitive checks and enhances readability.
Dynamic: Automatically adapts to the number of levels in your hierarchy.
Make sure to adapt the syntax according to your database's specific capabilities and syntax.