on 09-07-2016 7:29 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
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.