Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

ModifyUserDataJSON failing while doing concat

mbh_it
Regular Contributor II
Regular Contributor II

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

5 REPLIES 5

SudheerKaneti
New Contributor II
New Contributor II

Hi Mahesh,

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

Thank you.

Saathvik
All-Star
All-Star

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

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

mbh_it
Regular Contributor II
Regular Contributor II

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
Regular Contributor II
Regular Contributor II

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

mbh_it
Regular Contributor II
Regular Contributor II

solution use:

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