Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/16/2024 04:24 AM
Hello All,
Here is the dataset we are trying to utilise to populate Identity's CustomProperty13.
Here is the Pre-Processor Query being used in the Modify User JSON of the UserImport.
{
"ADDITIONALTABLES": {
"USERS": "SELECT city,statuskey,customproperty30,costcenter,country,customproperty32,customproperty33,employeetype,companyname,systemusername,customproperty28,customproperty27,customproperty50,customproperty7,enddate,employeeid,customproperty23,location,email,departmentname,departmentnumber,title,firstname,lastname,customproperty21,customproperty9,customproperty10,customproperty29,customproperty42,termdate,customproperty31,preferedfirstname,customproperty2,middlename,customproperty39,customproperty16,customproperty55,customproperty58,customproperty59,customproperty60,customproperty61,customproperty62,customproperty63,username,manager,customproperty1,customproperty54,customproperty47,customproperty48,customproperty44,customproperty6,customproperty49,customproperty52,customproperty15,customproperty17 from USERS",
"DATASET_VALUES": "SELECT datasetkey,attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute9,attribute10 from dataset_values"
},
"COMPUTEDCOLUMNS": [
"customproperty13",
"customproperty62"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty62 = now()",
"UPDATE NEWUSERDATA nu SET customproperty13 = CASE WHEN ((SELECT COUNT(trim(cv.attribute7)) FROM CURRENTDATASET_VALUES cv WHERE cv.datasetkey = 22 and cv.attribute1 = nu.departmentnumber and trim(cv.attribute5) = trim(nu.customproperty33) and cv.attribute6 = nu.title and nu.country = 'United States') = 1) THEN (SELECT trim(cv.attribute7) FROM CURRENTDATASET_VALUES cv WHERE cv.datasetkey = 22 AND cv.attribute1 = nu.departmentnumber AND trim(cv.attribute5) = trim(nu.customproperty33) AND cv.attribute6 = nu.title AND cv.country = 'United States') ELSE '' END"
]
}
Upon running the import, the CP13 is not being populated and I don't see any error in the logs but the value is somehow always satisfying the ELSE Condition. How can I troubleshoot this? Please let me know. Thanks
Solved! Go to Solution.
07/16/2024 10:56 PM
Validate from data analyzer
07/19/2024 05:52 AM
@rushikeshvartak wrote:Validate from data analyzer
Here is how I was able to validate from dataanalyser and add the missing piece to the SQL query. Adding here for reference.
select attribute7, username from dataset_values dv, users u where datasetkey=22 and dv.attribute1=u.departmentnumber and dv.attribute5=u.customproperty33 and dv.attribute6=u.title and country !='United States' and dv.attribute3 !='US'
select attribute7, username from dataset_values dv, users u where datasetkey=22 and dv.attribute1=u.departmentnumber and dv.attribute5=u.customproperty33 and dv.attribute6=u.title and country ='United States' and dv.attribute3 ='US'
07/17/2024 04:33 AM
@SowmithriV Looks query correct , check datasets key and hardcoded values correct or wrong?
07/19/2024 05:39 AM
Hi @SowmithriV ,
Looking at your preprocessor, the script structure looks fine.
How to troubleshoot this?
07/19/2024 05:47 AM - edited 07/19/2024 05:48 AM
Thank you all for the response. I did try to run this query against my datanalyser and was able to see understand that my query was returning two values which is why it always satisfied the ELSE condition.
Here is the working query
"UPDATE NEWUSERDATA SET customproperty13 = CASE WHEN ((SELECT COUNT(trim(attribute7)) FROM CURRENTDATASET_VALUES WHERE datasetkey = 22 and trim(attribute1) = trim(departmentnumber) and trim(attribute5) = trim(customproperty33) and trim(attribute6) = trim(title) and trim(COUNTRY) = 'United States' and attribute3='US') = 1) THEN (SELECT trim(attribute7) FROM CURRENTDATASET_VALUES WHERE datasetkey = 22 and trim(attribute1) = trim(departmentnumber) and trim(attribute5) = trim(customproperty33) and trim(attribute6) = trim(title) and trim(COUNTRY) = 'United States' and attribute3='US') WHEN ((SELECT COUNT(trim(attribute7)) FROM CURRENTDATASET_VALUES WHERE datasetkey = 22 and trim(attribute1) = trim(departmentnumber) and trim(attribute5) = trim(customproperty33) and trim(attribute6) = trim(title) and trim(COUNTRY) != 'United States' and attribute3 != 'US') = 1) THEN (SELECT trim(attribute7) FROM CURRENTDATASET_VALUES WHERE datasetkey = 22 and trim(attribute1) = trim(departmentnumber) and trim(attribute5) = trim(customproperty33) and trim(attribute6) = trim(title) and trim(COUNTRY) != 'United States' and attribute3 != 'US') ELSE 'CouldNOTFetchAdminWebGroupName' END"