MODIFYUSERJSON FROM WORKDAY

[Joon]

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 ?

[Joon]

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"
	]
}

[rushikeshvartak]

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

[Joon]

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

[rushikeshvartak · Accepted solution]

{
"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"
]
}

[Joon]

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"

]

}