Preprocessor Query using Datasets

[SowmithriV]

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 

[rushikeshvartak · Accepted solution]

Validate from data analyzer

[SowmithriV]

---

@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'

[Raghu]

@SowmithriV  Looks query correct , check datasets key and hardcoded values correct or wrong?

[PremMahadikar]

Hi @SowmithriV ,

Looking at your preprocessor, the script structure looks fine.

How to troubleshoot this?

• I would suggest you run individual select statements with hardcoded values from import and check if the response is correct. (check below, and hardcode the new alias values)
• As mentioned above, the query always executes else statement, then it means the structure of the script is right.
• It's with the data and the query to be corrected.
• One suggestion: use different alias for both select statements.

"UPDATE NEWUSERDATA new SET customproperty13 = CASE WHEN ((

SELECT COUNT(trim(cv.attribute7)) FROM CURRENTDATASET_VALUES cv WHERE cv.datasetkey = 22 and cv.attribute1 = new.departmentnumber and trim(cv.attribute5) = trim(new.customproperty33) and cv.attribute6 = new.title and new.country = 'United States'

) = 1) THEN (

SELECT trim(cv1.attribute7) FROM CURRENTDATASET_VALUES cv1 WHERE cv1.datasetkey = 22 AND cv1.attribute1 = new.departmentnumber AND trim(cv1.attribute5) = trim(new.customproperty33) AND cv1.attribute6 = new.title AND cv1.country = 'United States'

) ELSE '' END"

If this helps, please consider selecting Accept As Solution and hit Kudos

[SowmithriV · Accepted solution]

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"