Click HERE to see how Saviynt Intelligence is transforming the industry.
|
Click HERE to see how Saviynt Intelligence is transforming the industry.
|
09/21/2023
08:22 AM
- last edited on
09/22/2023
02:42 AM
by
Sunil
Hi,
I am trying to populate some user attributes using Inline Preprocessor but getting error while running job.
Please find Inline Preprocessor query below:
Solved! Go to Solution.
09/22/2023 10:33 AM
Hi @manish97sh
If you remove the last line is it working fine and all fields are getting updated?
("UPDATE NEWUSERDATA AS nu SET nu.CUSTOMPROPERTY51= (SELECT DISTINCT CURRENTBSUNITDS.A1 FROM CURRENTBSUNITDS WHERE CURRENTBSUNITDS.A2 = nu.CUSTOMPROPERTY14))
If yes then dont use the Alias as A2, use the column name directly as Attribute2 and try it out.
Thanks
Darshan
09/22/2023 10:58 AM
Hi @Darshanjain ,
I was able to fix that issue and it is working fine now but i am facing issue in another query in Pre-Processor.
Please find JSON below:
{
"ADDITIONALTABLES": {
"USERS": "SELECT EMAIL, USERNAME, DISPLAYNAME, CUSTOMPROPERTY21, ENDDATE, CUSTOMPROPERTY44, COMPANYNAME, CUSTOMPROPERTY4, CUSTOMPROPERTY1, CUSTOMPROPERTY20, LOCATIONNUMBER, LOCATION, OWNER, CUSTOMPROPERTY54, CUSTOMPROPERTY60, CUSTOMPROPERTY55, CUSTOMPROPERTY14 FROM USERS",
"LOCATIONDS": "SELECT ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9 FROM DATASET_VALUES WHERE DATASETNAME = 'DS_IMPORT_LOCATION_MAPPING' OR datasetname = 'DS_Userform_NonWorkdayMarkets_Mapping'",
"ACCOUNTS": "SELECT NAME,CUSTOMPROPERTY52,CUSTOMPROPERTY9,CUSTOMPROPERTY2 FROM ACCOUNTS",
"COMPANYDOMAINDS": "SELECT ATTRIBUTE1 as companyname, ATTRIBUTE3 as domainname FROM DATASET_VALUES WHERE DATASETNAME='DS_Import_Domain_Mapping' and ATTRIBUTE1 is not null and ATTRIBUTE1 !=''",
"SITENAMEDOMAINDS": "SELECT ATTRIBUTE2 as sitename, ATTRIBUTE3 as domainname FROM DATASET_VALUES WHERE DATASETNAME ='DS_Import_Domain_Mapping' and ATTRIBUTE2 is not null and ATTRIBUTE2 != ''",
"VENDORCOMPANYDS": "SELECT ATTRIBUTE1, ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'DS_IMPORT_VENDORCOMPANY_MAPPING' ",
"BSUNITDS": "SELECT ATTRIBUTE1, ATTRIBUTE2 FROM DATASET_VALUES WHERE DATASETNAME = 'DS_Import_BusinessUnit_Mapping'"
},
"COMPUTEDCOLUMNS": [
"OWNER",
"LOCATION",
"COUNTRY",
"CUSTOMPROPERTY3",
"REGION",
"CUSTOMPROPERTY30",
"CUSTOMPROPERTY56",
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY60",
"CUSTOMPROPERTY55",
"CUSTOMPROPERTY54",
"CUSTOMPROPERTY21",
"LOCATIONNUMBER",
"CUSTOMPROPERTY51",
"TERMDATE"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.OWNER = (SELECT CURRENTUSERS.USERNAME FROM CURRENTUSERS WHERE CURRENTUSERS.EMAIL = nu.CUSTOMPROPERTY20 limit 1)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.LOCATION = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE2 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.COUNTRY = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE3 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY3 = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE4 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.REGION = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE5 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY30 = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE6 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY56 = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE7 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY8 = (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE8 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = nu.LOCATIONNUMBER)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY60 = CASE WHEN nu.CUSTOMPROPERTY30 IS NOT NULL AND nu.CUSTOMPROPERTY30 != '' THEN CONCAT('OU=USERS,OU=USERS,OU=',nu.CUSTOMPROPERTY30,',DC=DEVGUWW,DC=NET') ELSE cu.CUSTOMPROPERTY60 END",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY55 = (SELECT DISTINCT CURRENTVENDORCOMPANYDS.ATTRIBUTE2 FROM CURRENTVENDORCOMPANYDS WHERE CURRENTVENDORCOMPANYDS.ATTRIBUTE1 = nu.COMPANYNAME)",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY54 = cu.CUSTOMPROPERTY54",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.LOCATIONNUMBER = CASE WHEN (cu.LOCATIONNUMBER = '' AND cu.CUSTOMPROPERTY44 = '') THEN (SELECT CURRENTLOCATIONDS.ATTRIBUTE1 from CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE2 = nu.LOCATION) ELSE nu.LOCATIONNUMBER END",
"UPDATE NEWUSERDATA AS nu LEFT JOIN CURRENTUSERS AS cu ON nu.USERNAME = cu.USERNAME SET nu.CUSTOMPROPERTY51= (SELECT DISTINCT CURRENTBSUNITDS.ATTRIBUTE1 FROM CURRENTBSUNITDS WHERE CURRENTBSUNITDS.ATTRIBUTE2 = nu.CUSTOMPROPERTY14)",
"update newuserdata nu left join currentusers cu on cu.email = nu.customproperty20 set nu.customproperty21 = CONCAT(cu.displayname, '; ', cu.customproperty21)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.TERMDATE = CASE WHEN (NEWUSERDATA.ENDDATE IS NULL OR NEWUSERDATA.ENDDATE = '') then '' ELSE ADDDATE(ADDDATE(NEWUSERDATA.ENDDATE, interval 23 HOUR), interval (SELECT DISTINCT CURRENTLOCATIONDS.ATTRIBUTE9 FROM CURRENTLOCATIONDS WHERE CURRENTLOCATIONDS.ATTRIBUTE1 = NEWUSERDATA.LOCATIONNUMBER) MINUTE) END",
"CUSTOMFUNCTION###GENERATEUPN"
],
"CUSTOMFUNCTIONS": {
"GENERATEUPN": {
"FULLCLASSNAME": "com.diageo.saviynt.utility.UserTransformation",
"METHODNAME": "generateUniqueUPN"
}
}
}
I am getting error as below:
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847576606Z stderr F 21-Sep-2023 21:45:13.847 WARNING [quartzScheduler_Worker-7] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_466519 SET TEMPNEWUSERS_466519.TERMDATE = CASE WHEN (TEMPNEWUSERS_466519.ENDDATE IS NULL OR TEMPNEWUSERS_466519.ENDDATE = '') then '' ELSE ADDDATE(ADDDATE(TEMPNEWUSERS_466519.ENDDATE, interval 23 HOUR), interval(SELECT DISTINCT TEMPLOCATIONDS_466519.ATTRIBUTE9 FROM TEMPLOCATIONDS_466519 WHERE TEMPLOCATIONDS_466519.ATTRIBUTE1 = TEMPNEWUSERS_466519.LOCATIONNUMBER) MINUTE) END because: Data truncation: Incorrect datetime value: 'Nov 22, 2023 00:00:00'"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847787706Z stdout F 2023-09-21 21:45:13,847 [quartzScheduler_Worker-7] ERROR services.ImportSAvDataUserService - Error while processing data: "
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847806506Z stdout F com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Nov 22, 2023 00:00:00'"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847813306Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4206)"
"ecm-worker","2023-09
-21T21:45:14.294+00:00","2023-09-21T21:45:13.847817506Z stdout F at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847820606Z stdout F at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847824006Z stdout F at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847827706Z stdout F at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847831306Z stdout F at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1759)"
"ecm-worker","2023-09-21T21:45:14.294+00:00","2023-09-21T21:45:13.847834406Z stdout F at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)"
09/25/2023 03:04 AM
HI @manish97sh
make sure the end date column is same as term data, looks like a format issue
Thanks
Darshan
