Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/05/2024 11:42 PM - edited 06/05/2024 11:46 PM
Hi,
I am working on a preprocessor for user bulk upload:
{
"ADDITIONALTABLES": {
"USERS": "SELECT userkey,employeeid,username,costcenter,employeetype,customproperty10, customproperty60,customproperty29 from users",
"ACCOUNTS": "select accountid, name, accountkey, customproperty15 from accounts",
"ACCOUNT_ENTITLEMENTS1": "SELECT accountkey,entitlement_valuekey FROM account_entitlements1",
"ENTITLEMENT_VALUES":"Select entitlement_value,entitlement_valuekey from entitlement_values "
},
"COMPUTEDCOLUMNS": [
"employeeid",
"accountid",
"costcenter",
"employeetype",
"customproperty10",
"customproperty60",
"accountkey",
"entitlement_valuekey",
"customproperty29",
"name"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET costcenter= (select u.costcenter from CURRENTUSERS u where u.username=NEWUSERDATA.owner)",
"UPDATE NEWUSERDATA SET customproperty10= (select replace(accountID, substring_index(accountid, 'OU', 1),'') from CURRENTACCOUNTS where name = NEWUSERDATA.customproperty29)",
"UPDATE NEWUSERDATA SET employeetype = 'Contractor'",
"UPDATE NEWUSERDATA SET customproperty60= (select group_concat(ev.ENTITLEMENT_VALUE) as ID from CURRENTACCOUNTS a, CURRENTACCOUNT_ENTITLEMENTS1 ae1 , CURRENTENTITLEMENT_VALUES ev where a.accountkey = ae1.accountkey and ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY and a.name = NEWUSERDATA.customproperty29 group by a.name)"
]
}
When I use the above, it says unknown a.accountkey column
Solved! Go to Solution.
06/06/2024 12:21 AM
06/06/2024 10:11 AM
Hi @Shubhamjain27 , Can you share the final query you used it will help others
Thanks