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
