Same table multiple times in Pre processor

[rushikeshvartak]

Can we have same table multiple times in preprocessor 

UPDATE ACCOUNTS AS A
JOIN ENDPOINTS AS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
JOIN USERS AS U ON U.USERNAME = A.CUSTOMPROPERTY18
JOIN USERS AS M ON U.OWNERONTERMINATE = M.USERKEY
SET A.CUSTOMPROPERTY19 = M.SITEID,
A.CUSTOMPROPERTY20 = M.USERNAME,
A.CUSTOMPROPERTY21 = SYSDATE()
WHERE E.ENDPOINTNAME = 'ABCD'
AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND M.SITEID IS NOT NULL
AND (U.CUSTOMPROPERTY8 = CURDATE() OR U.CUSTOMPROPERTY13 = CURDATE());

Need help in MODIFYUSERDATAJSON

[Dhruv_S]

Hi @rushikeshvartak 

As long as the query is correct as per SQL, it should work.

Could you please test it for any one user and see if you get any error in the logs.

Regards,

Dhruv Sharma

[rushikeshvartak]

what will be synatx in ADDITIONALTABLES 

USERS1 USERS2 ?

[rushikeshvartak · Accepted solution]

It worked with below synatax 

Updating manager name in CP64

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,USERNAME , MANAGER, OWNER, SITEID,JOBCODE, STATUSKEY, CUSTOMPROPERTY13, CUSTOMPROPERTY64,DISPLAYNAME,OWNERONTERMINATE,FIRSTNAME,LASTNAME FROM USERS",
"USERS1": "SELECT USERKEY,USERNAME , MANAGER, OWNER, SITEID,JOBCODE, STATUSKEY, CUSTOMPROPERTY13, CUSTOMPROPERTY64,DISPLAYNAME,OWNERONTERMINATE,FIRSTNAME,LASTNAME FROM USERS",
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY64",

],
"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA nUser INNER JOIN CURRENTUSERS xUser ON xUser.USERNAME=nUser.USERNAME INNER JOIN CURRENTUSERS1 xUserManager ON xUser.MANAGER=xUserManager.USERKEY SET nUser.CUSTOMPROPERTY64 =CONCAT(xUserManager.FIRSTNAME,' ',xUserManager.LASTNAME)",

]
}