on 10-22-2013 7:43 AM
Hi,
Please someone could help !!!
Step 1 :
CREATE FUNCTION ABCD.scale(xobjid nvarchar)
RETURNS TABLE (FRM INT, TOO INT, objid1 nvarchar) LANGUAGE SQLSCRIPT AS
BEGIN
--declare xobjid1 nvarchar(8);
--xobjid1 := :xobjid;
RETURN SELECT FRM, TOO, :xobjid as objid1 FROM ABCD.CUSTOMERCONNECTIONS;
END;
Step 2 :
CREATE PROCEDURE ABCD.ARRAY_AGG_TEST()
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
BEGIN
DECLARE idd NVARCHAR ARRAY;
DECLARE n,x Integer;
DECLARE idd1 NVARCHAR;
tab = select ID from ABCD.CUSTOMER;
idd := ARRAY_AGG(:tab.ID);
n := CARDINALITY(:idd);
--select :n as card from dummy;
--output := :idd;
--rst = UNNEST(:idd);
--SELECT * FROM :rst;
for x in 1 .. :n do
idd1 := :idd[1];
select * from ABCD.scale(:idd1);
--select FRM, TOO, objid1 from I050447.scale('1A');
end for;
END;
Step 3: Then do call:
CALL ABCD.ARRAY_AGG_TEST();
I get the following error:
Could Not Execute 'Call ABCD.ARRAY_AGG_TEST()' in 268 ms.
SAP dbtech jdbc 1301 numeric or value error: ABCD.ARRAY_AGG_TEST(): (range 3)
Thanks Much,
Abhishek
Hi Abhishek,
This has to be create from my end and to see.
Could you please send the DDL for the tables CUSTOMER & CUSTOMERCONNECTIONS.
If the table columns are huge then send the data size and types of columns which you used.
Regards
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
With SPS06 only write operations on Array are supported.
http://help.sap.com/hana/Whats_New_SAP_HANA_Platform_Release_Notes_en.pdf
Regards,
Vivek
I copy pasted your code to create tables and procedure was created successfully, can't attach screenshot now:
CREATE PROCEDURE ARRAY_AGG_TEST1()
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
BEGIN
DECLARE idd NVARCHAR ARRAY;
DECLARE n,x Integer;
DECLARE idd1 NVARCHAR;
tab = select ID from CUSTOMER1;
idd := ARRAY_AGG(:tab.ID);
n := CARDINALITY(:idd);
--select :n as card from dummy;
--output := :idd;
--rst = UNNEST(:idd);
--SELECT * FROM :rst;
for x in 1 .. :n do
idd1 := :idd[1];
select * from scale1(:idd1);
--select FRM, TOO, objid1 from I050447.scale('1A');
end for;
END;
Statement 'CREATE PROCEDURE ARRAY_AGG_TEST1() LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE idd ...'
successfully executed in 136 ms 502 µs (server processing time: 135 ms 211 µs) - Rows Affected: 0
Regards,
Vivek
Hi Abhishek,
Yeah after inserting data it is giving same error as mentioned by you
The problem is you are taking nvarchar as input to function scale and it is not able to process
idd1 := :idd[1];
But if you take any integer as input it works fine:
CREATE FUNCTION scale2 (val INT) RETURNS TABLE (FRM INT, WEIGHT INT)
LANGUAGE SQLSCRIPT AS
BEGIN
RETURN SELECT FRM, :val * WEIGHT AS WEIGHT FROM CUSTOMERCONNECTIONS1;
END;
CREATE PROCEDURE ARRAY_AGG_TEST1()
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
BEGIN
DECLARE idd integer ARRAY;
DECLARE n,x Integer;
DECLARE idd1 integer;
tab = select WEIGHT from CUSTOMER1;
idd := ARRAY_AGG(:tab.WEIGHT);
n := CARDINALITY(:idd);
idd1 := :idd[1];
select * from scale2(:idd1);
END;
call ARRAY_AGG_TEST1() works fine and gives output
There is no need to use for loop as you are taking idd[1], using for loop it gives 5 output results.
Regards,
Vivek
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.