Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/17/2024 12:08 PM
Hi Team,
We have a requirement where if User email is present in Saviynt but empty/null in the import HR source data, it should not update the email for the User during import,
whereas if email is abc@sav.com in Saviynt and xyz@sav.com in HR source table, it should update the email in Saviynt accordingly.
We have written the below code in ModifyJson but it is not working as expected, it updates the value of email to blank/null in Saviynt if it is blank in DB source during import.
"UPDATE NEWUSERDATA, CURRENTUSERS SET CURRENTUSERS.EMAIL=NEWUSERDATA.email where NEWUSERDATA.email IS NOT NULL and NEWUSERDATA.customproperty3 = CURRENTUSERS.customproperty3",
where CP3 is the reconciliation field.
04/17/2024 12:35 PM
Please try and run below query:
UPDATE CURRENTUSERS
SET CURRENTUSERS.EMAIL = NEWUSERDATA.email
FROM CURRENTUSERS
JOIN NEWUSERDATA ON CURRENTUSERS.customproperty3 = NEWUSERDATA.customproperty3
WHERE NEWUSERDATA.email IS NOT NULL AND NEWUSERDATA.email <> '';
Regards,
Krunal Kadam
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.
04/17/2024 05:24 PM
UPDATE CURRENTUSERS
SET CURRENTUSERS.EMAIL =
CASE
WHEN NEWUSERDATA.email IS NOT NULL AND NEWUSERDATA.email != '' THEN NEWUSERDATA.email
ELSE CURRENTUSERS.EMAIL
END
WHERE CURRENTUSERS.customproperty3 = NEWUSERDATA.customproperty3
04/21/2024 09:46 PM
Tried with both the queries shared above by @rushikeshvartak, @krunalkadam but still the Email is becoming 'NULL' or blank when it is coming as blank from the HR source.
04/22/2024 02:12 AM
Instead or preprocessor you can have case when condition in SQL select query
05/08/2024 12:52 PM
Hello,
Did you solve this issue? I am trying to do the same, but instead of updating the email, I need to set customproperty9, and it does not work... 😞
I have also tried both suggestions and neither works.
Thanks in advance,
Mihaela
Thank you
05/08/2024 01:16 PM
Hi @Miha, what is the recon field you are using ? Is it present in both the tables?
05/08/2024 04:18 PM
Hey,
Thanks for the fast reply. Yes, it is present in both tables, I am using the username.
05/08/2024 01:19 PM
Hi @sdey_2023 ,
The below code is working for me.
{
"ADDITIONALTABLES": {
"USERS": "SELECT username,email,customproperty3 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"email"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.email = CASE WHEN (NU.email != '' or NU.email != null) THEN NU.email ELSE (select cu.email from CURRENTUSERS cu where NU.customproperty3=cu.customproperty3 limit 1) END"
]
}
@Miha , please refer the same!
If this answers your question, please Accept As Solution and hit Kudos
05/08/2024 04:18 PM
Thank you, this worked for me!
"UPDATE NEWUSERDATA NU SET NU.CUSTOMPROPERTY9 = CASE WHEN (NU.CUSTOMPROPERTY9 != '' or NU.CUSTOMPROPERTY9 != null) THEN NU.CUSTOMPROPERTY9 ELSE (select cu.CUSTOMPROPERTY9 from CURRENTUSERS cu where NU.USERNAME=cu.USERNAME limit 1) END"