ModifyUserDataJson Query not updating value correctly

[sdey_2023]

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.

[krunalkadam]

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]

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

[sdey_2023]

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.

[rushikeshvartak]

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

[Miha]

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]

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

[Miha]

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]

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]

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"