User-Defined Field (UDF) Creation Unsuccessful
While creating a user-defined field (UDF) for a document on SAP Business One version for SAP HANA , the system gives an error and user-defined field (UDF) creation does not get successful. And we also see that an alter table statement hangs on HANA.
Note: SAP Business One is running on SAP HANA Rev 97 or other HANA revisions prior to Rev 102.01.
Solution
The above error is due to a programming error. During recompiling a procedure having "WITH RESULT VIEW" clause an invalid dependency information must be made. Therefore the 'alter table' command, issued by Business One for creating UDF would have been blocked by invalid object dependency.
The following steps should be taken to resolve this issue:
1. Before adding a column drop the procedure and then re-create the procedure after adding the column.
2. Procedure definition of the procedures having "WITH RESULT VIEW" clause should be changed to remove the "WITH RESULT VIEW" clause and then wrap the procedure with calculation view.
For Example:
CREATE PROCEDURE "my_procedure"(OUT VAR_OUT "out_variable")
LANGUAGE SQLSCRIPT
READS SQL DATA WITH RESULT VIEW "result_view_name" AS
BEGIN
...
END;
Firstly a procedure is needed to be dropped and it should be recreated without "WITH RESULT VIEW" clause like shown below, then wrap the procedure by creating the calculation view.
CREATE PROCEDURE "my_procedure"(OUT VAR_OUT "out_variable")
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
...
END;
CREATE COLUMN VIEW "result_view_name" TYPE CALCULATION WITH PARAMETERS ('PROCEDURE_SCHEMA' = 'procedure_schema', 'PROCEDURE_NAME'='your_procedure');
For cleaning up the invalid dependency:
Please run the following statement first for ensuring there is invalid object dependency:
Select * from sys.p_objectdependency_
left outer join views on dependentoid = view_oid
where baseoid = (select
table_oid
from tables
where table_name = <table name to create UDF on>
and schema_name = <schema name>)
and dependenttype = 2
and view_oid is null;
If the statement gives an results, please contact HANA development support (HAN-DB) to ensure that you have remote system accesss for them to help in cleaning up the invalid object dependency.
The programming error which leads to this issue is fixed in SAP HANA Rev 102.01. To prevent this issue from happening please contact SBO-BC-DB-HANA for upgrade options.