on 2012 Oct 11 3:48 PM
Hi experts,
I have some trouble with HANA/SQLScript.
I created a procedure like that one:
After creation I've tryed to call the procedure with something like
call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( "IA_PARENT_OF_BC", out_table ); or
call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( "IA_PARENT_OF_BC", :out_table );. or
call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( iv_linke_type_id="IA_PARENT_OF_BC", out_table );
But all the time I've got only error messages.
You find an example here.
Does anybody know why tha call statement in the embeded SQL editor doesn't work or what I've done wrong?
Thanks to your for helpfull every feedback,
Martin
Hi Martin,
Few points:
1) The input value of "IA_PARENT_OF_BC", which I believe is for LINK_TYPE_ID should be in single quotes 'IA_PARENT_OF_BC' and not double quotes " ".
2) If you are trying to call this from SQL editor then you might want to use the result view (I hope the procedure is defined with result view) like
select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"
with parameters
(
'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')
)
Regards,
Ravi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ravi,
thanks to your post.
If I use your statement I got a new error:
Could not execute 'select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" with parameters ( 'placeholder' = ...'
SAP DBTech JDBC: [259] (at 25): invalid table name: Could not find table/view d052319/GET_RELEVANT_ENTITIES in schema _SYS_BIC: line 1 col 26 (at pos 25)
So I think it isn't with result view. How could I define the result view output for the procedure? The creation script in the _SYS_BIC table is like the following:
create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,
out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" ) language SQLSCRIPT sql security definer reads sql data as /*********
Begin Procedure Script ************/
BEGIN ot_relevant_entries = SELECT
"SOURCE_MANDT",
"SOURCE_GUID_1",
"SOURCE_GUID_2",
"SOURCE_ID_1",
"SOURCE_ID_2",
"SOURCE_TYPE_ID",
"DESTINATION_MANDT",
"DESTINATION_GUID_1",
"DESTINATION_GUID_2",
"DESTINATION_ID_1",
"DESTINATION_ID_2",
"DESTINATION_TYPE_ID"
FROM "D052319"."ZTN_ENTITY_LINK"
WHERE 'LINK_TYPE_ID' = :iv_link_type_id
;
END
;
/*********
End Procedure Script ************/
Thanks for your help,
Martin
Hi Martin,
Can you please modify your create procedure statement to add RESULT VIEW like below. With the result view option, you would be able to fire SELECT * statement against the result view generated by the procedure. Please refer to page 16 of SQL script guide for the syntax details: http://help.sap.com/hana/hana_dev_sqlscript_en.pdf
create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,
out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" )
language SQLSCRIPT sql security definer reads sql data WITH RESULT VIEW v_relevant_entries
as
/*********
Begin Procedure Script ************/
BEGIN ot_relevant_entries = SELECT
"SOURCE_MANDT",
"SOURCE_GUID_1",
"SOURCE_GUID_2",
"SOURCE_ID_1",
"SOURCE_ID_2",
"SOURCE_TYPE_ID",
"DESTINATION_MANDT",
"DESTINATION_GUID_1",
"DESTINATION_GUID_2",
"DESTINATION_ID_1",
"DESTINATION_ID_2",
"DESTINATION_TYPE_ID"
FROM "D052319"."ZTN_ENTITY_LINK"
WHERE 'LINK_TYPE_ID' = :iv_link_type_id
;
END
;
/*********
End Procedure Script ************/
Regards,
Ravi
Hi Ravi,
thanks for your help, but by executing
select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"
with parameters
(
'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')
)
I still got the same error like before:
Could not execute 'select * from "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" with parameters ( 'placeholder' = ...'
SAP DBTech JDBC: [259] (at 25): invalid table name: Could not find table/view d052319/GET_RELEVANT_ENTITIES in schema _SYS_BIC: line 1 col 26 (at pos 25)
Could it be a problem that the HANA instance is on revision level 34 and the studio is on 38?
Did you have any other suggestions?
Best regards,
Martin
Hi Ravi,
thanks for your help but my problem isn't solved. My procedure is know that one:
create procedure "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" ( in iv_link_type_id VARCHAR(255) ,
out ot_relevant_entries "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES/tabletype/ot_relevant_entries" ) language SQLSCRIPT sql security definer reads sql data with result view v_relevant_entries as /*********
Begin Procedure Script ************/
BEGIN ot_relevant_entries = SELECT
"SOURCE_MANDT",
"SOURCE_GUID_1",
"SOURCE_GUID_2",
"SOURCE_ID_1",
"SOURCE_ID_2",
"SOURCE_TYPE_ID",
"DESTINATION_MANDT",
"DESTINATION_GUID_1",
"DESTINATION_GUID_2",
"DESTINATION_ID_1",
"DESTINATION_ID_2",
"DESTINATION_TYPE_ID"
FROM "D052319"."ZTN_ENTITY_LINK"
WHERE 'LINK_TYPE_ID' = :iv_link_type_id
;
END
;
So the result view sholud be created I think.
Now I try to select from it via:
select * from v_relevant_entries
with parameters
(
'placeholder' = ('$$iv_link_type_id$$', 'IA_PARENT_OF_BC')
)
I try to recompile the procedure with
ALTER PROCEDURE "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES" RECOMPILE;
I try again to call the procedure, but the error message
Could not execute 'select * from v_relevant_entries with parameters ( 'placeholder' = ('$$iv_link_type_id$$', ...'
SAP DBTech JDBC: [259] (at 14): invalid table name: Could not find table/view V_RELEVANT_ENTRIES in schema D052319: line 1 col 15 (at pos 14)
still exist. Also if I qoute the name of the result view with single or double quotes I got this error.
Any solution for that?
Best regards,
Martin
Hi Martin,
You are creating the procedure in _SYS_BIC schema, so I think the v_relevant_entries will be created in the same schema. But you are executing the procedure in D052319 schema hence it is not able to locate the result view.
You can add the schema name in the create procedure like
reads sql data with result view D052319.V_RELEVANT_ENTRIES as
...
And then try to select from the result view. BTW, one question, why did you create the procedure in _SYS_BIC schema ?
Regards,
Ravi
Hi Martin,
You have to use single quotes to pass values instead of double quotes..
call "_SYS_BIC"."d052319/GET_RELEVANT_ENTITIES"( 'IA_PARENT_OF_BC', out_table );
Can you try the above?
thanks,
Anooj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
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.