on 09-05-2013 9:48 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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)' );
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
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.
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
Hi
You can use OPTION(MAXRECURSION n)
Regards
NK
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.