Array iteration in SQL script.

[Former Member]

Hi Experts,

I have a table as follows :

SNO	VARIABLE	VALUE
1	x	10
2	x	20
3	x	30
4	y	40

I am getting x, y variable name using forllowing query:

VARIABLE_NAME= SELECT DISTINCT VARIABLE,VALUE FROM table_name ;

VARIABLE_NAME_ARRAY=ARRAY_AGG(:VARIABLE_NAME.VARIABLE); */

Now I want to get Values related to variable x in one array i.e., [10,20,30] and y in one more [40]

I am trying to achieve it using following code

FOR i IN 1 ..:ROW_COUNT-1

DO

VALUE_ARRAY = SELECT VALUE FROM table_name WHERE VARIABLE_NAME=:VARIABLE_ARRAY[:i];

END FOR;

but the problem here is SELECT statement is not accepting VARIABLE_ARRAY[:i] in WHERE clause and I want to

declare VALUE_ARRAY as dynamic variable as number of distinct variables may vary.

-> How can I declare variables dynamically or is there a way to declare 2 dimensional array and use it in my scenario?

I tried using CURSORS, again there I encounter the problem of dynamic variable declaration.

Could you guys please let me know how can I achieve this, or is there any other method to achieve this using SQLScript?

Regards,

Vijay.

[former_member187673 · Accepted solution]

Hi Vijay, what is the output that you are trying to get to? It's not clear from the above. Why do you need to use arrays and cursors? Have you tried using pure SQL to get your output?

Best practice is to avoid using cursors where possible.

Peter

[Former Member · Accepted solution]

Hi Peter,

I want to get all the values related to variable x i.e.,[10,20,30] and y [40]  and perform x*y i.e.,

10*40, 20*40, 30*40. So I have to get (10,40) and pass it to the function which performs (10*40) and so on...

In this case there are only 2 variables (x,y) but these number of variables may vary

for ex:

         x*y*z  and x[10,20], y[30], and z[40] here I ll have to pass (10,30,40) (20,30,40) to the function

which calculates (10*30*40). For this use case my table structure would be as follows :

SNO	VARIABLE	VALUE
1	x	10
2	x	20
3	y	30
4	z	40

and the query VARIABLE_NAME= SELECT DISTINCT VARIABLE FROM table_name ; result would be x,y,z.

I want to write an algorithm to achieve this kind of functionality irrespective of number of variables,

and also is there any provision to define multi dimensional arrays in SQLScript??

please do help me out..

Regards,

Vijay.                    

[former_member187673 · Accepted solution]

Hi Vijay, you may want to try some dynamic sql e.g.

DROP TABLE "XYZ_TEST";


CREATE COLUMN TABLE "XYZ_TEST" ("SNO" DECIMAL(10),"VAR_XYZ" NVARCHAR(10),"VAL" DECIMAL(10));


insert into "XYZ_TEST" values (1,'x', 10);
insert into "XYZ_TEST" values (2,'x', 20);
insert into "XYZ_TEST" values (3,'y', 30);
insert into "XYZ_TEST" values (4,'z', 40);


DROP  PROCEDURE XYZ_TEST_P;


CREATE PROCEDURE XYZ_TEST_P()
LANGUAGE SQLSCRIPT AS


  CURSOR c_xyz FOR select distinct var_xyz
  from "XYZ_TEST";

  inner_sql_string NVARCHAR(20000) := 'select * from ';
  outer_sql_string NVARCHAR(20000) := 'select ';

  i int default 0;

BEGIN

  FOR xyz_row AS c_xyz
  DO


  if i>0 then
  outer_sql_string := outer_sql_string|| ' * '||xyz_row.var_xyz;

  inner_sql_string := :inner_sql_string|| ',';
  else
  outer_sql_string := outer_sql_string || xyz_row.var_xyz;
  end if;



  inner_sql_string := :inner_sql_string|| ' (select val as '||xyz_row.var_xyz||' from xyz_test where var_xyz = '''||xyz_row.var_xyz||''') as '||xyz_row.var_xyz;

  i := i+1;
  END FOR;

  outer_sql_string := outer_sql_string || ' from (' ||inner_sql_string||')';


  EXECUTE IMMEDIATE (:outer_sql_string);

END;






call XYZ_TEST_P();

[Former Member · Accepted solution]

Thank you Peter,

it worked ....just for curiosity, can we declare multidimensional arrays in sql script ?...I have gone through most of the content of SAP_HANA_SQL_Script_Reference_en.pdf, I din't find any reference to multidimensional arrays..

Regards,

Vijay.

[former_member187673 · Accepted solution]

Not sure on that one Vijay, haven't come across it before.

Peter