Dynamic Attribute Query for Datasets

[HichamElk]

Hello,

In the dynamic attribute of my endpoint, I put a query to filter directly on a dataset.

The dataset values are constructed as follows: ES_PRO_1818_OS
12_ES_PRO_1818_OS

For example, we want to filter on the value _ES_ which corresponds to the country code we have on the user file.

Here's our filter query:

SELECT DISTINCT 
    CASE 
        WHEN ${companyid} != 200 THEN attribute1 
        ELSE 'CEO' 
    END AS ID 
FROM dataset_values 
WHERE datasetname = "X_RoleId" 
AND (attribute1 LIKE concat((SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '_%') 
     OR attribute1 LIKE concat('%_', (SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '_%')
     OR attribute1 = 'CEO')

This query works well but selects all roles containing ‘ES’.
However, our filter indicates that it requires _ES_ or ES_ brackets to select the role. So we end up with roles like :

AR_21_BESR_21 which should not be selected.

In the data analyzer, we put anti-slashes to try and restrict the _

So it looks like this query that works in the data analyser.

SELECT DISTINCT 
    CASE 
        WHEN ${companyid} != 200 THEN attribute1 
        ELSE 'CEO' 
    END AS ID 
FROM dataset_values 
WHERE datasetname = "X_RoleId" 
AND (attribute1 LIKE concat((SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '_\%') 
     OR attribute1 LIKE concat('%\_', (SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '_\%')
     OR attribute1 = 'CEO')

However, when we put it in the dynamic attribute, it no longer works and keep selecting all roles including 'ES' 

[rushikeshvartak]

SELECT DISTINCT 
    CASE 
        WHEN ${companyid} != 200 THEN attribute1 
        ELSE 'CEO' 
    END AS ID 
FROM dataset_values 
WHERE datasetname = "X_RoleId" 
AND (
     attribute1 LIKE concat((SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '\_%') ESCAPE '\'
     OR attribute1 LIKE concat('%\_', (SELECT u.CUSTOMPROPERTY10 FROM USERS u WHERE u.userkey = '${requestee}'), '\_%') ESCAPE '\'
     OR attribute1 = 'CEO'
)