HANA dynamic sql

[pm_witmond]

Hi,

I'm trying to create a dynamic SQL script to create and fill a table. We need this to create a pivot table in Hana because Hana doen't have a pivot function.

We can create a dynamic string and when we manually execute what's in the string everything works. But we need this to work without manual copy/past of course

This is the current script

DROP PROCEDURE "stock";

CREATE PROCEDURE "stock"() LANGUAGE SQLSCRIPT AS

BEGIN

Declare SqlString NVARCHAR(3500);

Declare sLengte NVARCHAR(200);

Declare dLengte DOUBLE;

Declare CURSOR packet for SELECT DISTINCT "U_Lengte" FROM OIBT WHERE TO_INTEGER("Quantity") > 0 order by "U_Lengte" ASC;

call pr_drop_table (UPPER('pakket'));

SqlString:='create table pakket(itemcode nvarchar(20),itemname nvarchar(100),maat nvarchar(20),fsc nvarchar(20),M3 float default 0 ';

    FOR c_row as packet DO

    dLengte:= c_row."U_Lengte";

    SqlString:= SqlString ||',"'|| TO_NVARCHAR(dLengte)||'" int default 0';

    END FOR;

SqlString:= SqlString ||');';

SqlString:=SqlString || ' insert into pakket (itemcode,itemname,maat,fsc) select distinct "ItemCode","ItemName",TO_NVARCHAR(TO_INT("U_Dikte"))||'' x ''||TO_NVARCHAR(TO_INT("U_Breedte")),COALESCE("U_Kenmerk_1",'''') from OIBT where "Quantity">0;';

select SqlString from dummy;

--EXECUTE IMMEDIATE (:SqlString);

END;

When we enable the EXECUTE line the script doesn't run anymore (sql error incorrect syntax near ";")

Please assist.

Paul

[former_member185132]

You're trying to execute two SQL statements (first the SELECT, then the INSERT). The EXECUTE syntax doesn't allow for this. You can execute only a single SQL statement in a single EXECUTE command.

[pm_witmond]

Thanks !

It's working now.