on 08-31-2015 3:16 PM
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
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.