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

[Ben_Mahfoudh]

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:

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

[Keijo_Karvonen · Accepted solution]

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

[pablo_rosero1]

Moved to Adobe Campaign Classic​