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

Need help to get Preprocessor Query for Manager Update

saiKrishna
Regular Contributor
Regular Contributor

Hi Team,

We have a requirement where manager field is `null for the users need to populate with unique ID of manager for such users.

Using below JSON for the same but it was throwing error, Can someone let me know if I am missing anything here.

{
"ADDITIONALTABLES": {

"USERS": "SELECT username,manager FROM USERS"
},

"COMPUTEDCOLUMNS": ["manager"],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA n left join CURRENTUSERS cu on n.manager=cu.userkey SET n.manager = '9' where cu.manager is NULL "
]
}

 

Regards,

Sri Ranga

 

8 REPLIES 8

rushikeshvartak
All-Star
All-Star
  • What is error?

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@rushikeshvartak Please find the error message from the Job level.

SriRanga_0-1715167884274.png

 

Hi @saiKrishna ,

In additional tables, add userkey column

"ADDITIONALTABLES": { "USERS": "SELECT userkey,username,manager FROM USERS" }

 

If this answers your question, please considers selecting Accept As Solution and hit Kudos

{
"ADDITIONALTABLES": {

"USERS": "SELECT userkey,username,manager FROM USERS"
},

"COMPUTEDCOLUMNS": ["manager"],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA n left join CURRENTUSERS cu on n.manager=cu.userkey SET n.manager = '9' where cu.manager is NULL "
]
}

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

saiKrishna
Regular Contributor
Regular Contributor

@rushikeshvartak @PremMahadikar  I was trying to execute below JSON import job was successful but no data was populated for the users, Can you please check if I am missing anything here

This was the actual ask check manager email in users table and pick corresponding username and map it for manager attribute. If username returned is null, assign default username

{
"ADDITIONALTABLES": {

"USERS": "SELECT userkey,username,manager,email FROM USERS"
},

"COMPUTEDCOLUMNS": ["manager"],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA n left join CURRENTUSERS cu on n.manager=cu.userkey SET n.manager =(case when cu.email IS NULL then '9' else cu.username end ) where cu.manager is NULL "
]
}

 

Raghu
Valued Contributor III
Valued Contributor III

@saiKrishna  try below : (case when cu.email IS NULL then '9' else cu.userkey end

Manager wil have only userkey but you mapped above thread username, you want username then u need to use 'Owner' column where manager using

 

{
"ADDITIONALTABLES": {

"USERS": "SELECT userkey,username,manager,email FROM USERS"
},

"COMPUTEDCOLUMNS": ["manager"],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA n left join CURRENTUSERS cu on n.manager=cu.userkey SET n.manager =(case when cu.email IS NULL then '9' else cu.userkey end ) where cu.manager is NULL "
]
}


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

saiKrishna
Regular Contributor
Regular Contributor

@Raghu I had already tired by hardcoding one of the userkey over there but job was success no data got updated.

@saiKrishna ,

In data analyzer, if manager attribute is queried - userkey is displayed. But here, you need to pass username to update.

The below code is working for me:

{
"ADDITIONALTABLES": {

"USERS": "SELECT userkey,username,manager,email FROM USERS"
},

"COMPUTEDCOLUMNS": [ "manager" ],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA N SET N.manager =(select case when CU.email IS NULL then 'premmahadikar' else CU.username end from CURRENTUSERS CU where N.manager=CU.username and CU.manager is NULL)"
]
}

Just FYI: During testing, my input csv file had below columns

username,statuskey,manager

I hope this solution answers your query, please consider selecting Accept As Solution and hit Kudos