Defining a recursive view in SAP HANA

[Former Member]

Hi All,

I have a problem defining a recursive view in HANA, in MS SQL for example I use the WITH statement to recursively iterate over the bill_of_materials view (see below), any idea how to do it in HANA?

WITH recursiveBOM
  
(assembly_id, assembly_name, parent_assembly) AS
(SELECT
parent.assembly_id,parent.assembly_name, parent.parent_assembly
FROM bill_of_materials parent
WHERE
parent.assembly_id=100
UNION ALL
SELECT child.assembly_id, child.assembly_name, child.parent_assembly
FROM recursiveBOM parent, bill_of_materials child
WHERE child.parent_assembly = parent.assembly_id)

SELECT assembly_id, parent_assembly, assembly_name
FROM recursiveBOM;

Thanks,

Tal

[former_member657531]

Hi please Help me for,

how to get All level BOM data in HANA.

[Former Member · Accepted solution]

Hi Tal, I believe HANA deals with recursion a bit differently:

"Compared to the classical definition of recursion in the SQL standard, SQLScript supports a generalized version of recursion. In the SQL standard the definition of a recursive view (using a WITH RECURSIVE-statement) is not parameterizable and does not support multiple output parameters. In SQLScript, recursion is defined on the procedure level (i.e., read-only procedures can call themselves) instead on the statement level. Thus, scalars and tables can be used as input parameters and a recursive procedure can produce multiple output parameters. Iterative problems can often be re-formulated using recursion. Thus, in the functional extension, we currently do not explicitly support a language construct for iteration. However, the procedural extension of SQLScript [SQL12] supports iteration (e.g., loops over result sets)."

More information on the following research paper by:

"SQLScript: Efficiently Analyzing Big Enterprise Data in SAP HANA" Page 374.

Here is another entry answered correctly by on the same topic:

http://scn.sap.com/thread/3257506

Cheers,

Esteban

[Former Member · Accepted solution]

Hi Esteban,

Thank you for the information, the research paper is very interesting.

However I need the recursion defined in the statement level of my SQL view and not via SQLScript.as stated above in the following sentence:

"In the SQL standard the definition of a recursive view (using a WITH RECURSIVE-statement) is not parameterizable and does not support multiple output parameters"

BTW, if the WITH RECURSIVE statement is in the SQL standard shouldn't HANA support it as well?

Thanks,

Tal.

[Former Member · Accepted solution]

Tal,

You are welcome. Regarding your need for it to be defined on the Statement Level, the document states that this is explicitly not possible ""In SQLScript, recursion is defined on the procedure level (i.e., read-only procedures can call themselves) instead oF the statement level.". (the F instead of an N is my editing, I believe its a type-o).

I´m currently on the same problem/research hence eventhough it looks like we are a dead end, if I find something I´ll be sure to post it here.

SQLscript being "ANSI compliant" is a broad description since ANSI SQL has a couple of variants or revisions. I´ve seen a couple of blog entries indicating that HANA SQLscript is ANSI 92 compliant and that would explain why recursive calls is not supported whilest being (SAP SQLscript) ANSI compliant (recursive calls were introduced on ANSI SQL 1999).

I have still to find a SQLscript ANSI compliance matrix,I´ll keep looking.

Cheers,

Esteban

[lbreddemann · Accepted solution]

Just to clarify: the option for self-calling procedures/functions are not available in the released SAP HANA product. The paper describes a prototypical implementation to present and discuss a specific design approach.

- Lars

[lbreddemann · Accepted solution]

You may want to look into hierarchy views (e.g. shown in )

Since you're a colleague in SAP you can request using the hierarchy views by SAP HANA development.

[Former Member]

Hey Tal,

I saw that you asked this question long time ago, were you able to find a solution for that?

I have a similar issue...

Thanks,

Heli

[former_member241304]

Hi Experts ,

Please help me i'm also facing same issue.

how we can overcome this in SAP HANA.

[Former Member]

Hello Tal,

I am curious to know if you were able to find a work around for your requirement. We have a similar requirement in my project to create a hana model for BOM. I would appreciate any help.

Thanks,

Aamod.