Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
10/11/2023 05:42 AM - edited 10/11/2023 05:43 AM
Hello team,
I want to use following in Workday connector to generate department name from CP65 & costcenter, I am trying this with csv upload first and planning to use same in modifyuserData at connector, not sure why it is failing, can someone guide me? Logs attached
UPDATE NEWUSERDATA SET DEPARTMENTNAME=(SELECT concat(CUSTOMPROPERTY65,'-',COSTCENTER) FROM CURRENTUSERS where NEWUSERDATA.username=CURRENTUSERS.username)
Thanks
Mahesh
Solved! Go to Solution.
10/11/2023 06:59 AM
Hi Mahesh,
Try with this query: "UPDATE NEWUSERDATA SET DEPARTMENTNAME=concat(CUSTOMPROPERTY65,'-',COSTCENTER)"
Thank you.
10/11/2023 08:29 AM
@mbh_it : Try below, I believe you are trying to concat CP65 and cost center from current user state, If so you can use below
UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU on NU.USERNAME=CU.USERNAME SET NU.DEPARTMENTNAME=concat(CU.CUSTOMPROPERTY65,'-',CU.COSTCENTER)
if you are just trying to concat CP65 and cost center from new user data then use below
UPDATE NEWUSERDATA NU SET NU.DEPARTMENTNAME=concat(NU.CUSTOMPROPERTY65,'-',NU.COSTCENTER)
10/11/2023 11:04 AM - edited 10/11/2023 11:07 AM
Hello Saathvik/ SudheerKaneti,
Thanks for your quick response.
I tried both of the options and it did not work.
Requirement:
1) Data import from workday and store costcenter code to CP65 of users table and costcenter value in costcenter field of users table.
2) Concat CP65-Costcenter Value.
This I am first testing via CSV upload of user , though same I will ultimately use it with workday MODIFYUSERDATA option at connection.
Used following three and did not work logs attached.
UPDATE NEWUSERDATA NU SET NU.DEPARTMENTNAME=concat(NU.CUSTOMPROPERTY65,'-',NU.COSTCENTER)
UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU on NU.USERNAME=CU.USERNAME SET NU.DEPARTMENTNAME=concat(CU.CUSTOMPROPERTY65,'-',CU.COSTCENTER)
UPDATE NEWUSERDATA NU SET NU.DEPARTMENTNAME=(select concat(CU.CUSTOMPROPERTY65,'-',CU.COSTCENTER) from Users CU);
10/11/2023 12:11 PM
This is resolved now., I read more on documentation .
10/11/2023 12:12 PM
solution use:
{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY65, Departmentname, COSTCENTER, firstname, lastname FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY65",
"COSTCENTER",
"Departmentname"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET Departmentname = CONCAT(CUSTOMPROPERTY65,'-',COSTCENTER)"
]
}