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
BedrockMission!

Learn More

View all

Sign in to view all badges

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

Avatar

Avatar
Validate 1
Level 1
Ben_Mahfoudh
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
Ben_Mahfoudh
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
Ben_Mahfoudh
Level 1

22-05-2018

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Boost 1
Level 1
Keijo_Karvonen
Level 1

Likes

2 likes

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Boost 1
Affirm 1
View profile

Avatar
Boost 1
Level 1
Keijo_Karvonen
Level 1

Likes

2 likes

Total Posts

2 posts

Correct Reply

1 solution
Top badges earned
Boost 1
Affirm 1
View profile
Keijo_Karvonen
Level 1

04-04-2019

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

Answers (1)

Answers (1)

Avatar

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct Reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct Reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile
pablo_rosero1
Level 10

04-04-2019