How to dynamically create column in Scripted calculation view

[Former Member]

Hi Team,

There is a need to create level hierarchy based on the BW Hierarchy table (H table) in HANA.

So we ideally replicate the same H table in HANA and then create a scripted calculation view to create level hierarchy.

Now I am able to write a static SQL statement to create a level hierarchy for 3 levels of hierarchy data.

But as we know there might be multiple level of hierarchies in BW table and script should be intelligent enough to create as many rows as per the levels in the H table.

In this case I think we need to use the dynamic sql statement. Can somebody help me how can we use dynamic SQL to generate columns dynamically in HANA?

Example:- If I have 5 levels in BW H table then SQLScript should be able to create 5 columns dynamically.

Appreciate your response here.

Thanks & Regards,

Anup

[former_member226419]

Hi Anup,

I didnt get the requirement completel. But for Dynamic SQL what I understood here is you want to select/create dynamic number of columns and for the same you can use 'EXEC and EXEC IMMEDIATE' statements to do the same.

And also kindly let me know your exact requirement and what code you have tried yet.

BR

Sumeet

[Former Member]

Hi Sumeet,

My requirement is to create SQL statement dynamically based on the levels maintained in BW hierarchy table.

If you know about BW hierarchy table structure we have a field that captures the level of the hierarchy. So I can use max(level) to identify the max level and then depending upon the no. of level I get, I want to create so many columns in the output.

Hope my question is clear now, if not please do let me know, I will be happy to answer all your query.

Thanks & Regards,

Anup

[former_member226419]

Can you please show your existing code?

BR

Sumeet

[Former Member]

Hi Sumeet,

Here you go.

SELECT MAIN1.LEVEL1, MAIN1.LEVEL2, MAIN2.LEVEL3 FROM
(SELECT S.NODENAME AS LEVEL1, F.LEVEL2 AS LEVEL2, F.NODEID
FROM (SELECT C.NODEID AS NODEID,C.NODENAME AS LEVEL2,C.PARENTID AS PARENTID
FROM "ANCHAUDHARY"."BIC_HZBUCCEPTY" C
WHERE C.TLEVEL IN ('2') AND HIEDID = 'ABC') AS F
INNER JOIN
(SELECT DISTINCT NODEID, NODENAME FROM "ANCHAUDHARY"."BIC_HZBUCCEPTY" WHERE HIEDID = 'ABC') AS S
ON F.PARENTID = S.NODEID) AS MAIN1


LEFT OUTER JOIN


(SELECT F.LEVEL3 AS LEVEL3,F.PARENT AS NODEID, S.NODENAME FROM
(SELECT C.NODENAME AS LEVEL3, C.PARENTID AS PARENT
FROM "ANCHAUDHARY"."BIC_HZBUCCEPTY" C
WHRE C.TLEVEL = '3' AND HIEDID = 'ABC') AS F INNER JOIN
(SELECT DISTINCT NODEID, NODENAME FROM "ANCHAUDHARY"."BIC_HZBUCCEPTY" WHERE HIEDID = 'ABC') AS S
ON F.PARENT = S.NODEID) AS MAIN2
ON MAIN1.NODEID = MAIN2.NODEID     

If you notice line 1 of the code, I have three columns defined as static. Now, I need to make this code dynamic to work for any levels of the hierarchy.

Thanks & Regards,

Anup

[Former Member]

Hello All,

Any pointers would be highly appreciated.

Regards,

Anup