Reports : How to pass variable as Array values in filtering conditions (query activitie) | Community
Skip to main content
B
Ben_Mahfoudh
May 22, 2018
Solved

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

  • May 22, 2018
  • 2 replies
  • 3405 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Keijo_Karvonen

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

2 replies

Keijo_Karvonen
Keijo_KarvonenAccepted solution
April 4, 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

    pablo_rosero1
    pablo_rosero1
    Level 9
    April 4, 2019

    Moved to Adobe Campaign Classic​

    Terms & ConditionsAccessibility statement

    Loading footer...