Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

MODIFYUSERJSON FROM WORKDAY

Joon
Regular Contributor II
Regular Contributor II

Hello,

I am working on to put manager in "OWNERONTERMINATE" field during User import job.

What I tried is.

{
	"ADDITIONALTABLES": {
		"USERS": "SELECT OWNERONTERMINATE,CUSTOMPROPERTY20, MANAGER FROM USERS"
	},
	"COMPUTEDCOLUMNS": [
		"OWNERONTERMINATE"
	],
	"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.OWNERONTERMINATE = (SELECT USERS.userkey FROM USERS WHERE USERS.userkey = NEWUSERDATA.manager)"
	]
}

but didn't work . can anyone help on this ?

5 REPLIES 5

Joon
Regular Contributor II
Regular Contributor II

This also does not work ....

{
	"ADDITIONALTABLES": {
		"USERS": "SELECT USERKEY,USERNAME FROM USERS"
	},
	"COMPUTEDCOLUMNS": [
		"OWNERONTERMINATE"
	],
	"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.MANAGER = CURRENTUSERS.USERKEY SET NEWUSERDATA.OWNERONTERMINATE=CURRENTUSER.USERKEY"
	]
}

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT USERNAME, MANAGER, OWNER FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
     "OWNERONTERMINATE"
  ],
  "PREPROCESSQUERIES": [
      "UPDATE NEWUSERDATA NU1 SET NU1.OWNERONTERMINATE = NU1.OWNER"
  ]
}

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Joon
Regular Contributor II
Regular Contributor II

Hello Rushikesh,

I would need Manager's data to create a condition.

E.g)

CASE WHEN Manager.customproperty1 = "true"

NU.OWNERONTEMINATE= NU.OWNER

ELSE -

NU.OWNERONTEMINATE='' END

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, MANAGER, OWNER,CUSTOMPROPETY1 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"OWNERONTEMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.OWNERONTEMINATE = CASE WHEN NU.CUSTOMPROPETY1 = 'true' THEN NU.OWNER ELSE '' END"
]
}


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Joon
Regular Contributor II
Regular Contributor II

Thanks Rushikesh,

below works for me

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY15, CUSTOMPROPERTY65, CUSTOMPROPERTY64, USERKEY,USERNAME,OWNERONTERMINATE,CUSTOMPROPERTY20, MANAGER FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1", "MANAGER","CUSTOMPROPERTY2","OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
 
"UPDATE NEWUSERDATA NU LEFT OUTER JOIN CURRENTUSERS CU ON NU.CUSTOMPROPERTY64=CU.USERNAME SET NU.OWNERONTERMINATE= CASE WHEN (CU.CUSTOMPROPERTY15 in ('')) then CU.USERKEY ELSE '' END"
 
]
}