Using multiple datasets in a condition within Modify user data json - Workday RAAS

[Arita]

Hi Team,

Need your inputs for the below usecase.

We have 2 datasets Dataset1,Dataset2 and we have to populate the value in CP42 based on conditions on these datasets as below

CP18 in Dataset1 then return exmaple.com

CP52 in Dataset2 then return example.com

Tried using multiple combinations of queries, it's not working. Samples are below.

1st:"UPDATE NEWUSERDATA JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = Dataset1.ATTRIBUTE4 SET NEWUSERDATA.CUSTOMPROPERTY42 = Dataset1.ATTRIBUTE1" or 
"UPDATE NEWUSERDATA JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = Datset2.ATTRIBUTE4 SET NEWUSERDATA.CUSTOMPROPERTY42 = Dataset2.ATTRIBUTE1";

2nd:

"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY18 = Dataset1.ATTRIBUTE4) THEN Dataset1.ATTRIBUTE1 WHEN (NEWUSERDATA.CUSTOMPROPERTY52 = Dataset2.ATTRIBUTE4) THEN Dataset2.ATTRIBUTE1 ELSE '' END"

Thanks

[rushikeshvartak · Accepted solution]

UPDATE NEWUSERDATA
LEFT JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = CURRENTDataset1.ATTRIBUTE4
LEFT JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = CURRENTDataset2.ATTRIBUTE4
SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE
WHEN CURRENTDataset1.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset1.ATTRIBUTE1
WHEN CURRENTDataset2.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset2.ATTRIBUTE1
ELSE ''
END;

[Arita]

Thank you for the reply. We wanted to test this query as well but before that only we started getting error with original JSON itself as below. Would you have an idea on what would have gone wrong and how to fix it. We raised a ticket also. 

{java.lang.NumberFormatException: For input string: "1,,,,,,,,,,,,,,,,,,,,,,,,,," at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Integer.parseInt(Integer.java:580) at java.lang.Integer.valueOf(Integer.java:766) at com.saviynt.custom.process.ABCCustomTransform.readProperties(ABCCustomTransform.java:111) at com.saviynt.custom.process.ABCCustomTransform.doCustomPreprocessPrimaryEmailID(ABCCustomTransform.java:674) at com.saviynt.ecm.services.ImportSAvDataUserService.callCustomPreProcessorMethod(ImportSAvDataUserService.groovy:562) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:407) 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:8696) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1720) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:155) 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)}

[Arita]

Hi Rushikesh,

We tested with the below query, but it is only working for CP18 condition and for second condition it is not working and moving to next logic in JSON. Any thoughts further ?

UPDATE NEWUSERDATA
LEFT JOIN CURRENTDataset1 ON NEWUSERDATA.CUSTOMPROPERTY18 = CURRENTDataset1.ATTRIBUTE4
LEFT JOIN CURRENTDataset2 ON NEWUSERDATA.CUSTOMPROPERTY52 = CURRENTDataset2.ATTRIBUTE4
SET NEWUSERDATA.CUSTOMPROPERTY42 = CASE
WHEN CURRENTDataset1.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset1.ATTRIBUTE1
WHEN CURRENTDataset2.ATTRIBUTE4 IS NOT NULL THEN CURRENTDataset2.ATTRIBUTE1
ELSE ''
END;

[rushikeshvartak]

Share full json

[Arita]

Hi Rushikesh,

Jar file issue is resolved. So logic is working now. thank you.