cancel
Showing results for 
Search instead for 
Did you mean: 

Recursive Query in HANA

0 Kudos

Hello,

In SQL SERVER i have a recursive query to compose a tree of Bill of materials SAP B1.

Please can you tell me how I can get the same query in HANA?

WITH RecursiveSQL as (

    SELECT 1 as Level,OITT.Code as ABSCODE,OITT.Code,ITT1.Code as ITEMCODE,ITT1.Quantity FROM OITT INNER JOIN ITT1 ON OITT.Code = ITT1.Father

    UNION ALL

    SELECT RecursiveSQL.Level + 1 as Level, RecursiveSQL.ABSCODE, OITT.Code,ITT1.Code as ITEMCODE,ITT1.Quantity FROM RecursiveSQL INNER JOIN OITT ON RecursiveSQL.ITEMCODE = OITT.Code INNER JOIN ITT1 ON OITT.Code = ITT1.Father

)

SELECT * FROM RecursiveSQL WHERE ABSCODE = '0021142-5252-3-00XXL' Order by Level

Many Thanks for your help.

Accepted Solutions (0)

Answers (2)

Answers (2)

Thanks for your comments, but I think OPTION (MAXRECURSION n) is not HANA syntax.

What I am looking for is the syntax for SAP HANA  to obtain the same result (a recursive query) as in SQL Server.

Thank you very much.

former_member184768
Active Contributor
0 Kudos

Hi Adria,

To my knowledge, currently there is no such feature directly available in HANA. You might have referred to the discussion thread http://scn.sap.com/thread/3384059 on this topic.

I think you can achieve your required functionality with HANA Procedure. I had a recursive requirement, which I tried to solve through the procedure as mentioned in the document here. You can check if the concept can be adopted for your requirement.

Regards,

Ravi

0 Kudos

Thank you very much for your comments,

The alternative seems to be faster to use a procedure but is inefficient and it will take time to implement in complex queries.

 

In Bratislava we were shown 'Father-Son Hierarchies' with a very basic example, do you think this can be used for the recursive query that I need?

 

If so, do you know where I can find more information / documentation on how to parameterize 'Father-Son Hierarchies' type queries?

I show the example of 'Father-Son Hierarchies' that was provided:

CREATE COLUMN TABLE h_mini_src ( pred CHAR(2), succ CHAR(2) PRIMARY KEY );

INSERT INTO h_mini_src VALUES ( null, 'A1' );

INSERT INTO h_mini_src VALUES ( 'A1', 'B1' );

INSERT INTO h_mini_src VALUES ( 'A1', 'B2' );

INSERT INTO h_mini_src VALUES ( 'B1', 'C1' );

INSERT INTO h_mini_src VALUES ( 'B1', 'C2' );

INSERT INTO h_mini_src VALUES ( 'B2', 'C3' );

INSERT INTO h_mini_src VALUES ( 'B2', 'C4' );

INSERT INTO h_mini_src VALUES ( 'C3', 'D1' );

INSERT INTO h_mini_src VALUES ( 'C3', 'D2' );

INSERT INTO h_mini_src VALUES ( 'C4', 'D3' );

CREATE COLUMN VIEW h_mini TYPE HIERARCHY WITH PARAMETERS (

'hierarchyDefinitionType' = 'select',

'hierarchyDefinition' = '{

"sourceType":"recursive",

"nodeType":"string",

"runtimeObjectType":"blob",

"sourceQuery":"SELECT pred, succ FROM h_mini_src" }' );

--Display all nodes and their attributes :

SELECT * FROM h_mini;

--Display all nodes subordinate to node B2 :

SELECT result_node FROM h_mini WITH PARAMETERS ( 'expression' = 'subtree("B2",1,99)' );

lbreddemann
Active Contributor
0 Kudos

Hi Adria,

you're right - this is an example of the hierarchy support in SAP HANA and if I'm not mistaken the example is taken out of the HA300 training course (right? at least it was the last time I presented the course).

Unfortunately the syntax for hierarchies is not yet officially released.

For your requirement, it might be possible to use this feature (not sure about the SAP business one data model, so I cannot comment on this).

However, using a SQLscript approach is definitively feasible here as you can create recursion with that.

- Lars

0 Kudos

Thanks for your cooperation.

I managed to understand how the Syntax of the 'Father-Son Hierarchies', but unfortunately I can not use recursive querys why in my column 'SUCC' can have duplicate values, otherwise in my hierarchical tree can be one branch duplicated.

Definitely will opt to substitute the recursion in SQL SERVER in HANA stored procedure.

Thank you very much.

felix_vincent
Discoverer
0 Kudos

I have a question regarding recursive sql statements and HANA sps 07...we tested a simple query in Hana studio and it worked fine..

with kn as (select user_name from users)

select * from kn

...

the output was as expected ..

but when we decided to create a 'user defined statement in the system information perspective and run the same query we got the follow error..

'error executing query

cannot iterate over result set rows : SAP DBTech JDBC :[257] (at 36) sql syntax error ....

this can be easily reproduce..Hana is on sps 07......

thanks

Felix

lbreddemann
Active Contributor
0 Kudos

Hey Vincent,

long time no see

I reckon this has to do with how HANA studio calls the query. Feel free to run a JDBC trace for that and/or open a support message.

I also would expect that this works.

- Lars

lbreddemann
Active Contributor
0 Kudos

sorry... support message ... what was I thinking??

It's "incident" nowadays, right?

felix_vincent
Discoverer
0 Kudos

thanks Lars...

it works when we run it in the sql console..would run the jdbc trace and then probably open an incident message...

thanks

Felix

Former Member
0 Kudos

Hi

You can use OPTION(MAXRECURSION n)

Regards

NK


former_member182114
Active Contributor
0 Kudos

Hi Naveenkumar,

I'm curious where did you found this option?

Regards, Fernando Da Rós

Former Member
0 Kudos

Hi,

You can see this function in SQL server 2008.