Enhanced Query for JOB to Update Account CP based on dataset value

[prasadpatil16]

Hello Experts,

I need help in developing enhanced query for below senario,

We have dataset with Department, country and Role. we want to update the XYZ app Accounts CP10 attribute based on camparing users Department and Country with Dataset Department, country  and result of roles need to be updated in value of CP10.

I have tried below query for single user and need help to build it for all users

SELECT (select GROUP_CONCAT(d.attribute3) from dataset_values d where datasetkey=5 and attribute1=(select ud.departmentname as id from users ud where ud.userkey=22) and d.attribute2=(select ud.customproperty1 as id from users ud where ud.userkey=22)) AS ACCOUNTS__CUSTOMPROPERTY10,a.accountkey as accounts__primarykey FROM ACCOUNTS AS a where a.ENDPOINTKEY = 203 and a.name = 'XYZ'

Please let me know if any additional information is required.

Thanks,

Prasad

[rushikeshvartak]

Can you share sample output from data analyzer

SELECT a.accountkey AS accounts__primarykey,
(SELECT GROUP_CONCAT(d.attribute3)
FROM dataset_values d
JOIN users ud ON d.attribute1 = ud.departmentname AND d.attribute2 = ud.customproperty1
WHERE d.datasetkey = 5
AND ud.userkey = a.accountkey) AS ACCOUNTS__CUSTOMPROPERTY10
FROM ACCOUNTS AS a
WHERE a.ENDPOINTKEY = 203
AND a.name = 'XYZ';