ModifyUserDataJSON failing while doing concat

[mbh_it]

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

[SudheerKaneti]

Hi Mahesh,

Try with this query: "UPDATE NEWUSERDATA SET DEPARTMENTNAME=concat(CUSTOMPROPERTY65,'-',COSTCENTER)"

Thank you.

[Saathvik]

@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)

[mbh_it]

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);

[mbh_it · Accepted solution]

This is resolved now., I read more on documentation .

[mbh_it · Accepted solution]

solution use:

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY65, Departmentname, COSTCENTER, firstname, lastname FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY65",
"COSTCENTER",
"Departmentname"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET Departmentname = CONCAT(CUSTOMPROPERTY65,'-',COSTCENTER)"
]
}