cancel
Showing results for 
Search instead for 
Did you mean: 

Assign cursor value to variable

pm_witmond
Participant
0 Kudos

Hi,

I'm trying to create a dynamic sql statement using a cursor but all I get are error's

Please assist.

CREATE PROCEDURE "stock LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

Declare SqlString nvarchar(3500);


Declare CURSOR packet for SELECT DISTINCT "U_Length" FROM "OIBT" WHERE "Quantity" > 0 ;

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

    FOR cursor_row as packet DO

        SqlString := :SqlString || ',' || CAST(cur_row.U_Length AS nvarchar(4)) || ' int default 0';

    END FOR;

    SqlString := :SqlString || ')';

   EXEC(:sqlstring);

  

END;

The error is  invalid identifier: CURSOR_ROW:

Thanks,

Paul

Accepted Solutions (0)

Answers (2)

Answers (2)

pm_witmond
Participant
0 Kudos

Hi Lars,

Still facing problems and I really don't see what I'm doing wrong

CREATE PROCEDURE "stock"() LANGUAGE SQLSCRIPT AS

BEGIN

Declare SqlString NVARCHAR(3500);

Declare sLengte NVARCHAR(20);

Declare CURSOR packet for SELECT DISTINCT "U_Lengte" FROM "IMPORT_LIVE"."OIBT" WHERE "Quantity" > 0 order by "U_Lengte" ASC;

    FOR c_row as packet DO

    sLengte:= TO_NVARCHAR(TO_DOUBLE(c_row."U_Lengte"));

    END FOR;

END;

The error is this :

Could not execute 'CREATE PROCEDURE "stock"() LANGUAGE SQLSCRIPT AS BEGIN Declare SqlString NVARCHAR(3500); Declare ...' in 741 ms 817 µs .

SAP DBTech JDBC: [2048]: column store error: [34011] failed to save calculation scenario : The following errors occured: Inconsistent calculation model (34011)

Details (Errors):

- CalculationNode ($$DUMMY$$): Internal compiler error at "IMPORT_LIVE"."stock": line 9 col 5 (at pos 278).

lbreddemann
Active Contributor
0 Kudos

No idea about that one.

Are you sure you have all access rights on the "IMPORT_LIVE" schema?

Would make things a bit easier, if you'd stick to one program logic to implement while chasing one coding mistake after the other...

lbreddemann
Active Contributor
0 Kudos

You defined the loop variable for the FOR loop as CURSOR_ROW but in the loop you try to access it via CUR_ROW.

Make up your mind

pm_witmond
Participant
0 Kudos

Hi Lars,

Yes I saw it after hitting the button:

CREATE PROCEDURE "stock" LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

Declare SqlString nvarchar(3500);

Declare CURSOR packet for SELECT DISTINCT "U_Lengte" FROM "OIBT" WHERE "Quantity" > 0 ;

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

    FOR cursor_row as packet DO

        SqlString := :SqlString || ',' || CAST(cursor_row.U_Length AS nvarchar(4)) || ' int default 0';

    END FOR;

    SqlString := :SqlString || ')';

   EXEC(:sqlstring);

 

END;

--The error is  invalid identifier: CURSOR_ROW:

lbreddemann
Active Contributor
0 Kudos

Alright.

First off: either type "U_Lengte" or "U_Length"...

And if your column names absolutely need to be mixed case then they always require to be called with double quotes.

So ... CAST (cursor_row."U_Length" AS ...  will work.

And as always: beware of dynamic SQL!

It's barely ever the best way to do stuff.