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 Query not updating value correctly

sdey_2023
Regular Contributor
Regular Contributor

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.

9 REPLIES 9

krunalkadam
New Contributor III
New Contributor III

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.

rushikeshvartak
All-Star
All-Star

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


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

sdey_2023
Regular Contributor
Regular Contributor

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.

Instead or preprocessor you can have case when condition in SQL select query 


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

Miha
New Contributor III
New Contributor III

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

NM
Regular Contributor III
Regular Contributor III

Hi @Miha, what is the recon field you are using ? Is it present in both the tables?

Miha
New Contributor III
New Contributor III

Hey,

Thanks for the fast reply. Yes, it is present in both tables, I am using the username.

The query specified by @PremMahadikar  worked!
 
Thank you!

PremMahadikar
Valued Contributor
Valued Contributor

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

Miha
New Contributor III
New Contributor III

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"