on 11-06-2013 4:41 PM
Hi,
Can some body help me to what I am currently trying to do is possible in HANA ?
My end requirement is something similar to the post
http://scn.sap.com/docs/DOC-40409
http://scn.sap.com/message/13986059#13986059
http://scn.sap.com/thread/3417970
Followed some of existing post; but could not solve the error which I am currently getting.
http://scn.sap.com/message/13797032
http://scn.sap.com/thread/3317284
Below is what I tried to do explained in a best way I can.
I would like to know If I can use the HANA Hierarchy inside the Calculated View as below ?
Calculated View 2: (Top) CALL_H_TEST2
/********* Begin Procedure Script ************/
BEGIN
var_out =
SELECT
QUERY_NODE,
RESULT_NODE,
LEVEL,
LEVEL_NAME,
ORDINAL,
IS_LEAF,
QUERY_NODE_NAME,
RESULT_NODE_NAME,
PATH,
PARENTS,
CHILDREN
FROM
"_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"
( PLACEHOLDER."$$LOCALIDCONTEXTITEM$$" => :LOCALIDCONTEXTITEM,
PLACEHOLDER."$$LOCALIDAUTHORITY$$" => :LOCALIDAUTHORITY
);
END /********* End Procedure Script ************/
Calculated View 1: (Lower) CALL_H_TEST1
The Hierarchy "_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" is created over Calculated View 1
which generates Parent Child hierarchical Table type data output.
When I view the Calculated View 2: providing default values for :LOCALIDCONTEXTITEM and :LOCALIDAUTHORITY
The Error is as below
Same is the case when I directly execute the sql script by providing values
SELECT
*
FROM
"_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"
( PLACEHOLDER."$$LOCALIDCONTEXTITEM$$" => 80000,
PLACEHOLDER."$$LOCALIDAUTHORITY$$" => 90000
);
ERROR:
Could not execute 'SELECT * FROM "_SYS_BIC"."CII_AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" ( ...' in 72 ms 188 µs .
SAP DBTech JDBC: [2048]: column store error: search table error: [6929] Hierarchy source data: Format error;PopHierarchyCreateSelect pop1,SQL: column store error: search table error: [34092] search on calculation model requires parameters;Required variable $$LOCALIDCONTEXTITEM$$ is not set. Dimension [TEST_H1] hierarchy [TEST_H1].[TEST_H1]
Can somebody clarify to me if its possible to use HANA Hierarchy created over Calculated View with PLACEHOLDERS and use then inside Calculated View ?
Thank You, I appreciate
Shaik
Message was edited by: Aj Shaik
Why are you using the PLACEHOLDER syntax? I'd expect that the following would work.
My advice when creating calc views is always to create a SQLScript procedure that works, first, then to port that code into a Calc View. It makes debugging much easier.
John
/********* Begin Procedure Script ************/
BEGIN
var_out =
SELECT
QUERY_NODE,
RESULT_NODE,
LEVEL,
LEVEL_NAME,
ORDINAL,
IS_LEAF,
QUERY_NODE_NAME,
RESULT_NODE_NAME,
PATH,
PARENTS,
CHILDREN
FROM
"_SYS_BIC"."AS/CALL_H_TEST1/AS_HEI_TEST1/hier/AS_HEI_TEST1" WHERE CONTEXTITEM = :contextitem and AUTHORITY = :authority;
END /********* End Procedure Script ************/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi John,
Sorry, actually I edited my question for better understanding of what I need dont know why the changes are not reflected.
Let me show what I tried to do.
I have:
1) CALL_H_TEST1 calculated view which generates table suitable for creating hierarchy by calling a stored procedure TEST_H2 which consumes parameters CONTEXTITEM and AUTHORITY
CALL "_SYS_BIC"."AS/TEST_H2" (:CONTEXTITEM, :AUTHORITY, HIERARHCYTBL) WITH OVERVIEW;
On CALL_H_TEST1 calculated view i have created HANA Heirarchy.
as shown below
As you suggested I tried to execute the script which is present in Calculated View 2 separately
SELECT
*
FROM
"_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1"
( PLACEHOLDER."$$CONTEXTITEM$$" => 80000,
PLACEHOLDER."$$AUTHORITY$$" => 90000
);
Following error:
Could not execute 'SELECT * FROM "_SYS_BIC"."AS/CALL_H_TEST1/TEST_H1/hier/TEST_H1" ( ...' in 89 ms 573 µs .
SAP DBTech JDBC: [2048]: column store error: search table error: [6929] Hierarchy source data: Format error;PopHierarchyCreateSelect pop1,SQL: column store error: search table error: [34092] search on calculation model requires parameters;Required variable $$CONTEXTITEM$$ is not set. Dimension [TEST_H1] hierarchy [TEST_H1].[TEST_H1]
Wondering if the HANA Hierarchy can be used along with internal PARAMETERS ?
If it is can you please provide the syntax ?
Thank You,
shaik
The thing is hierarchy node generates information of
QUERY_NODE,
RESULT_NODE,
LEVEL,
LEVEL_NAME,
ORDINAL,
IS_LEAF,
QUERY_NODE_NAME,
RESULT_NODE_NAME,
PATH,
PARENTS,
CHILDREN
over the PRED/SUCC hierachy table.
If I select on Calculated View I can only get the information of PRED/SUCC. But what i need is information which is generated by HANA Hierarchy node.
I currently dont know the syntax of how to get Hierarchy node information by calling Calculated View directly .
Please let me know if you have any idea.
Thanks for following up, I appreciate
Regards,
Shaik
Hi Shaik,
While this won't help you much for now, I did want to mention that lots of hierarchy-specific SQL extensions *should* become available in SP7 (can't promise) which should give access to useful hierarchy functions as well as the capacity to pass input parameters. (As for the specific syntax for passing parameters, unfortunately I don't recall at the moment - but it should be documented in SP7. Fingers crossed.)
Cheers,
Jody
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.