Dataset Query Validation for Update scenario

[Shubhamjain27]

Hi,

We have a usecase where we need to update cp8 of the user when location is updated.

I am using the below in preprocessor:

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 = (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1')"

and it gives an error "Subquery returns more than 1 row".

If i hardcode the location attribute like below, it works good.:

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 = (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper('North Canton OH') and CDV.DATASETNAME='Location_Details1')"

Any idea?

[NM]

Hi @Shubhamjain27 try this

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 IN (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1

[Shubhamjain27]

Used:

UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 IN (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1')"

Giving syntax error

[NM]

What error do you see in logs??

Have you defined dataset table in additional column??

Send full preprocessor query.

[Shubhamjain27]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,statuskey,CUSTOMPROPERTY5,CUSTOMPROPERTY1,COSTCENTER,TITLE,LOCATION,CUSTOMPROPERTY2,CUSTOMPROPERTY3,CUSTOMPROPERTY4,CUSTOMPROPERTY6,CUSTOMPROPERTY7,CUSTOMPROPERTY8,CUSTOMPROPERTY10,CUSTOMPROPERTY11,CUSTOMPROPERTY12,CUSTOMPROPERTY13,CUSTOMPROPERTY14,CUSTOMPROPERTY15,CUSTOMPROPERTY16,CUSTOMPROPERTY17,CUSTOMPROPERTY18,CUSTOMPROPERTY19,CUSTOMPROPERTY20,CUSTOMPROPERTY60,street,city,state,EMPLOYEETYPE FROM USERS",
"DATASET_VALUES": "SELECT ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,DATASETNAME FROM DATASET_VALUES"
},
"COMPUTEDCOLUMNS": [
"statuskey",
"LOCATION",
"COSTCENTER",
"CUSTOMPROPERTY5",
"COMPANYNAME",
"EMPLOYEETYPE",
"DEPARTMENTNAME",
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY9",
"CUSTOMPROPERTY10",
"CUSTOMPROPERTY11",
"CUSTOMPROPERTY12",
"CUSTOMPROPERTY13",
"CUSTOMPROPERTY14",
"CUSTOMPROPERTY15",
"CUSTOMPROPERTY16",
"CUSTOMPROPERTY17",
"CUSTOMPROPERTY18",
"CUSTOMPROPERTY19",
"CUSTOMPROPERTY20",
"CUSTOMPROPERTY60",
"STREET",
"city",
"state"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 IN (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1')"
]
}

We have multiple queries to add and same is working in new account creation. Its a copy from new to update connection.

Error:

[NM]

Try this

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 IN (select concat(''',CDV.attribute2,''') from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1')"

]

}

[Shubhamjain27]

Giving syntax error

[Shubhamjain27 · Accepted solution]

Worked:

added limit:

UPDATE NEWUSERDATA SET CUSTOMPROPERTY8 = (select CDV.attribute2 from CURRENTDATASET_VALUES CDV where Upper(CDV.attribute1) = Upper(NEWUSERDATA.LOCATION) and CDV.DATASETNAME='Location_Details1' limit 1)

[NM]

If attribute1 contains location and it would be specific to a single user while updating in preprocessor, weird that it returns multiple rows 

[Shubhamjain27]

Correct, IDK why.

Same issue was posted before as well:

https://forums.saviynt.com/t5/identity-governance/dataset-query-validation/m-p/92999