Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/22/2024 01:05 PM
Hi Team,
We have a requirement where we need to replace any qualifiers from the names of imported Users like MR. , JR. , Sr. , II, IV, X which mostly appear as prefix or suffix of names.
e.g - Robert Viv IV should convert to Rober Viv,
Downey JR. should convert to Downey
Is there any way to achieve this during import via preprocessor/Modify JSON?
04/22/2024 03:06 PM
You can update same
UPDATE users SET name = REGEXP_REPLACE(name, '(^|\s)(MR\.|JR\.|Sr\.|II|IV|X)(\s|$)', ' ')
04/23/2024 01:29 PM - edited 04/23/2024 01:58 PM
Tried the query but it is failing with error, added in comment the details.
04/23/2024 01:37 PM - edited 04/23/2024 01:44 PM
Can we nest REGEXP_REPLACE with REPLACE
04/23/2024 01:49 PM
04/23/2024 01:53 PM - edited 04/23/2024 01:58 PM
@rushikeshvartak Getting the following error when trying to import:
uploadUserFinalStep.returnMap=[Creating-Temp-Table-CURRENTUSERS-time:313 ms, Failure_msg:Error in Users Import - Error while processing data: FUNCTION ssminlp.REGEXP_REPLACE does not exist, Dropping-Temp-Table-NEWUSERDATA-time:9 ms, Dropping-Temp-Table-CURRENTUSERS-time:11 ms, Total_Preprocessing_Time:407 ms, Existing_Users:5555, Number_of_Records_in_Feed:1, Users_In_Feed:0, Active_Users_not_in_feed:5555, Users_Inserted:0, Users_Updated:0, Users_Inactivated:0, Users_Count:0, Total_Import_Time:2231 ms, Success:false]
04/23/2024 02:55 PM - edited 04/23/2024 02:55 PM
I tested this using file import, it worked.
Can you try below:
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME,FIRSTNAME,LASTNAME FROM USERS"
},
"COMPUTEDCOLUMNS":
[ "USERNAME"
],
"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET NEWUSERDATA.lastname = REGEXP_REPLACE(NEWUSERDATA.lastname, '(^MR.|JR.|Sr.|II|IV|X)(s|$)', ' ')"
]
}
04/23/2024 07:26 PM
Test using file import as suggested by @PremMahadikar and let us know if you face issues
04/23/2024 10:32 PM
@PremMahadikar @rushikeshvartak tried using csv file import only, but getting this error:
Error while processing data: FUNCTION ssminlp.REGEXP_REPLACE does not exist
We are using v24.2 of Saviynt.
Do we need to import or enable any function/table for using REGEXP_REPLACE
04/23/2024 10:35 PM
No
04/23/2024 10:38 PM
This is the detailed error message:
java.sql.SQLSyntaxErrorException: FUNCTION ssminlp.REGEXP_REPLACE does not exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8698) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1716) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)
04/23/2024 10:44 PM
Its issue with your csv file
04/23/2024 11:14 PM
I am using below csv:
04/23/2024 11:24 PM - edited 04/23/2024 11:25 PM
@sdey_2023 , Please try below attached file.
04/23/2024 11:50 PM
Unfortunately, it is the same error. Should we raise a ticket with Saviynt?