Error in Executing String Query in SAP B1 Version for HANA

[former_member286591]

Dear All,

I'm facing some error while executing a procedure in HANA System. My requirement is to calculate output based on Formula defined at UDT.

Formula will be change dynamically, but the Input Parameters are same for all the Formula.

The Logic is perfectly working in SQL Server System. But when i developed query for HANA, its throwing an error.

Request All of them to Help me on this.

Please find the below Procedure for Formula Calculation :-

CREATE PROCEDURE TEST_V1

(IN SalesType1 decimal(19, 2), IN Additional1 decimal(19, 2))

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

Begin

Declare Formula nvarchar(5000)='';

Select "U_Formula" Into Formula From "SBODEMOIN"."@IK_FORMULA";

/*

-- (:NetSalesType + (:NetSalesType * :Addtn)/100) -- This is the Formula from Table

*/

Exec('

Declare NetSalesType Decimal(19,2) = 0.0;

Declare Addtn Decimal(19,2) = 0.0;

NetSalesType := ' || :SalesType1 || ';

Addtn := ' || :Additional1 || ';

Select ' || :Formula || ' From Dummy

');

End;

--Drop Procedure SBODEMOIN."TEST_V1"

--Call SBODEMOIN."TEST_V1" (1000,10)

[Former Member]

It would be helpful if you provide the error you receive.

[former_member286591]

Hi Martin,

Please find the below error message :-

Could not execute 'Call SBODEMOIN."TEST_V1" (1000,10)' in 5 ms 463 µs .

SAP DBTech JDBC: [257]: sql syntax error: "SBODEMOIN"."TEST_V1": line 10 col 1 (at pos 334): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "Declare": line 2 col 1 (at pos 3)

[lucas_oliveira]

Hi Vijeesh,

EXEC cant be used that way. On HANA you'd need to have one EXEC per statement. Each EXEC is a contained block of execution, allowing one statement only. Declaring variables is also not possible as far as I could see (anyone else please correct me if I'm wrong here).

For that reason I don't think you can use dynamic calculation expressions within EXEC / EXECUTE IMMEDIATE. I believe this is feature is not there yet.

Check the example below:

DO BEGIN
DECLARE FORMULA NVARCHAR(5000) = '';
DECLARE VAL1 DECIMAL(19,2) = 10.0;
DECLARE VAL2 DECIMAL(19,2) = 30.0;

SELECT '(:VAL1 + :VAL2)' INTO FORMULA FROM DUMMY;
EXECUTE IMMEDIATE 'SELECT '|| :FORMULA || ' FROM DUMMY';
END;

This would fail with:

"[...] cannot use parameter variable: VAL1:[...]"

BRs,

Lucas de Oliveira