Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Reports : How to pass variable as Array values in filtering conditions (query activitie)

Ben_Mahfoudh
Level 1
Level 1

Hello,

Can we use a variable as value in a filtering condition with "is included in" as Operator ?

In a script activity I initiate a variable as follows:

ctx.vars.operationIdList = new Array(-1432383860,-1426727378,-1411155661);

Then i am trying to filter my query using this variable as a list:

1492585_pastedImage_1.png

I get the following error:

[xtk:report Error] PGS-220000 PostgreSQL error: ERROR: invalid input syntax for integer: "-1432383860,-1426727378,-1411155661" ...

Obviously my integer field does not like to be included in a string entree. So how can I cast this string to array using query Functions or other ?

Best regards

Ben mahfoudh

1 Accepted Solution
Keijo_Karvonen
Correct answer by
Level 1
Level 1

Old thread but I had the same problem.

Found out that you can use string function Charindex to check if value is in array:

1. in Javascript code, store the content of the array as string in variable. For example vars.cities = ['London', 'Paris', 'Berlin'].join(',');

2. in Query activity, add a filtering condition

3. as the Expression of the filtering condition, enter Charindex($(vars/@cities) , @city)

4. as the Operator of the filtering condition, enter greater than or equal to

5. as the Value of the filtereing condition, enter 1

Now then the query is executed:

- if value of @city is found in the string of cities, value of 1 or greater is returned and the row is included in the result

- if value of @city is not found in the string of cities, value of 0 is returned and the row is not included in result

View solution in original post

0 Replies
Keijo_Karvonen
Correct answer by
Level 1
Level 1

Old thread but I had the same problem.

Found out that you can use string function Charindex to check if value is in array:

1. in Javascript code, store the content of the array as string in variable. For example vars.cities = ['London', 'Paris', 'Berlin'].join(',');

2. in Query activity, add a filtering condition

3. as the Expression of the filtering condition, enter Charindex($(vars/@cities) , @city)

4. as the Operator of the filtering condition, enter greater than or equal to

5. as the Value of the filtereing condition, enter 1

Now then the query is executed:

- if value of @city is found in the string of cities, value of 1 or greater is returned and the row is included in the result

- if value of @city is not found in the string of cities, value of 0 is returned and the row is not included in result

View solution in original post