Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

PREPROCESSQUERIES Sets value to null without matching condition

angela
New Contributor III
New Contributor III

‎03/01/2024 08:31 AM - last edited on ‎03/01/2024 09:12 AM by Community Manager

Hi,

I am experiencing an issue with the PREPROCESSQUERIES in the ModifyUserJSON. I am trying to clear a value from a customproperty value when the data matches what was imported into a users location. We have been seeing values clear even when the conditions are not met

 

{
    "ADDITIONALTABLES": {
        "USERS": "SELECT USERKEY, username, lastname, owner, leaveStatus, employeeType, departmentname, costcenter, jobCode, displayname, location, state, customproperty1, customproperty29, customproperty35, customproperty36, customproperty37, customproperty38, customproperty39, customproperty40, customproperty63, customproperty64, customproperty65 FROM USERS"
    },
    "COMPUTEDCOLUMNS": [
        "customproperty40"
    ],
    "PREPROCESSQUERIES": [
        "UPDATE NEWUSERDATA INNER JOIN currentusers ON currentusers.username = newuserdata.username SET newuserdata.customproperty40 = null WHERE currentusers.customproperty40 = newuserdata.location"
    ]
}
 
I ran the query in the data analyzer and confirmed the result set that should clear - but we are seeing all values clear - even those not returned by the query. Has anyone come across a solution to this?
 
SELECT currentusers.username as 'cur username', newuserdata.username as 'newusername', currentusers.customproperty40,newuserdata.location FROM USERS NEWUSERDATA
INNER JOIN USERS currentusers ON currentusers.username = newuserdata.username
WHERE currentusers.customproperty40 = newuserdata.location
 
I've seen similar questions raised in the following:
 

Here is some parts of the log:

 

Processing query: UPDATE TEMPNEWUSERS_495340 INNER JOIN TEMPUSERS_495340 ON TEMPUSERS_495340.username = TEMPNEWUSERS_495340.username SET TEMPNEWUSERS_495340.customproperty40 = null WHERE TEMPNEWUSERS_495340.username = TEMPUSERS_495340.username AND TEMPUSERS_495340.customproperty40 = TEMPNEWUSERS_495340.location
2024-03-01/15:55:48.629 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Records updated: 2
2024-03-01/15:55:48.629 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Executing query to fetch the updated data list: SELECT USERNAME,DEPARTMENTNAME,LOCATION,CUSTOMPROPERTY1,COSTCENTER,JOBCODE,OWNER,CUSTOMPROPERTY40 FROM TEMPNEWUSERS_495340
2024-03-01/15:55:48.630 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Records fetched: 4
-------
----
---
2024-03-01/15:55:48.862 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - This is update user TESTUSER1 before setting the dirtyMap
2024-03-01/15:55:48.862 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - propNamesTemp = [customproperty40, jobID, savUpdateDate, savUpdateDate]
2024-03-01/15:55:48.862 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - #1 :: TESTUSER1 :: #Adding update user to the batch
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Final Batch - Processing a Update User TESTUSER2
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Enter processAttributes
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - processing attributes for user TESTUSER2, userattribute = null , updateuser = true
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - ChangedAttrs = [:]
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Exit processAttributes
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - dirtyMap=[customproperty40:[AB, null]], dirtyMap=1
2024-03-01/15:55:48.929 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - #3 :: TESTUSER2 :: #Update user :: 0 :: 0
2024-03-01/15:55:48.950 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - statusCheck: false
2024-03-01/15:55:48.950 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Inside Dirtymap condition
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Saving user data for username : TESTUSER2
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Final Batch - Processing a Update User TESTUSER3
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Enter processAttributes
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - processing attributes for user TESTUSER3, userattribute = null , updateuser = true
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - ChangedAttrs = [:]
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Exit processAttributes
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - dirtyMap=[customproperty40:[AB, null]], dirtyMap=1
2024-03-01/15:55:48.973 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - #3 :: TESTUSER3:: #Update user :: 0 :: 1
2024-03-01/15:55:48.987 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - statusCheck: false
2024-03-01/15:55:48.987 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Inside Dirtymap condition
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Saving user data for username : TESTUSER3
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - Final Batch - Processing a Update User TESTUSER4
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Enter processAttributes
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - processing attributes for user TESTUSER4, userattribute = null , updateuser = true
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - ChangedAttrs = [:]
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  INFO  services.ImportSAvDataUserService - Exit processAttributes
2024-03-01/15:55:49.010 [{}] [https-jsse-nio-443-exec-1007]  DEBUG services.ImportSAvDataUserService - dirtyMap=[customproperty40:[AB, null]], dirtyMap=1
0 Kudos
1 REPLY 1

AmitM
Valued Contributor
Valued Contributor

‎03/01/2024 12:00 PM

Hi @angela , raise a fresdesk ticket. Reading other forums also seems like an issue.

Meanwhile , to solve the problem for now, use java code in preprocessor to clear cp 40 for those conditions. 

Thanks,

Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

0 Kudos
Copyright © 2024 Khoros, LLC