cancel
Showing results for 
Search instead for 
Did you mean: 

Using arrays in the WHERE clause

Former Member
0 Kudos

Hi Gurus,

  I need your help with a syntax error in a script based calculation view. I have the following script that reads the CSKS table and gets a list of CostCenters (KOSTL) in to an array called dept_list. I want to use the list of values in that array in a following select statement in the WHERE clause.

   Activation fails at the ":dept_list". Any thoughts?

/********* Begin Procedure Script ************/

BEGIN

    declare dept_list NVARCHAR(10) ARRAY;

   

  dept_list = select "KOSTL" from "MYSCHEMA"."CSKS" where "KOSAR" = 'A';

  var_out = SELECT * FROM <View name>

               where "CostCenter" in :dept_list;

END /********* End Procedure Script ************/

Thanks,

Ajay.

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

Hello Ajay,

arrays cannot be used in such a way in SQLScript. Already your first select will fail.

You can either use a table variable


BEGIN

     dept_list = select "KOSTL" from "MYSCHEMA"."CSKS"

                     where "KOSAR" = 'A';

     var_out = select * from <view_name>

                    where "CostCenter" in (select "KOSTL" from :dept_list);

END

or directly integrate the first select in the second one:


BEGIN

     var_out = select * from <view_name>

                    where "CostCenter" in (select "KOSTL" from "MYSCHEMA"."CSKS" where "KOSAR" = 'A');

END

Regards,

Florian

Answers (0)