Using MODIFYUSERDATAJSON by uploading users using schema upload

[WilliamD]

Hello,

I am trying to use the property MODIFYUSERDATAJSON by Uploading Users using Schema Upload.

I set content of MODIFYUSERDATAJSON like this:

"COMPUTEDCOLUMNS":["termDate"],"PREPROCESSQUERIES":["UPDATE NEWUSERDATA SET termDate ='09.12.2021'"]

because I want to replace a date as the date has not the correct format in the hr file:

The format in the HR file: 2021-12-08 01:21:44.517000. This format is not correct for the termdate

Can you please help me to get this feature working ?

Thanks

[sudeshjaiswal]

Hello @WilliamD,

Can you please try to use date in yyyy-mm-dd format while setting it
For eg. 
"COMPUTEDCOLUMNS":["termDate"],"PREPROCESSQUERIES":["UPDATE NEWUSERDATA SET termDate ='2021-12-09'"]

[WilliamD]

Hello  sudeshjaiswal,

Thanks a lot for your answer,

Unfortunately, I still have the same error:
java.sql.BatchUpdateException: Data truncation: Data too long for column 'TERMDATE' at row 1

It is like the PREPROCESSQUERIES":["UPDATE NEWUSERDATA SET TERMDATE ='2021-12-09'"]} does not make any changes.

I attached the SAV file if it can help.

Regards

William

[WilliamD]

#USER_NOT_IN_FILE_ACTION=NOACTION
#FILENAME=users
#DELIMITER=,
#SKIP_NUMBER_OF_LINES=1
#ZERODAYPROVISIONING=TRUE
#userReconcillationField=UserName
#GENERATESYSTEMUSERNAME=FALSE
#GENERATEEMAIL=FALSE
#EMAILTEMPLATE="Email Template Name"
#CHECKRULES=TRUE
#MODIFYUSERDATAJSON={"ADDITIONALTABLES":{"USERS":"SELECT USERNAME, ORGUNITID FROM USERS","ACTIVEIDENTITIES":"SELECT DISTINCT USERNAME FROM USERS U LEFT JOIN USER_ACCOUNTS UA ON UA.USERKEY = U.USERKEY LEFT JOIN ACCOUNT_ENTITLEMENTS1 AE ON UA.ACCOUNTKEY = AE.ACCOUNTKEY LEFT JOIN ENTITLEMENT_VALUES EV ON EV.ENTITLEMENT_VALUEKEY = AE.ENTITLEMENT_VALUEKEY LEFT JOIN ACCOUNTS A ON A.ACCOUNTKEY=UA.ACCOUNTKEY WHERE ((UPPER(EV.CUSTOMPROPERTY1) LIKE '%SWITZERLAND%' AND EV.STATUS=1 AND A.STATUS='1') OR UPPER(U.COUNTRY) = 'SWITZERLAND')"},"COMPUTEDCOLUMNS":["TERMDATE"],"PREPROCESSQUERIES":["UPDATE NEWUSERDATA SET TERMDATE ='2021-12-09'"]}
CUSTOMPROPERTY18,USERNAME,FirstName,LastName,CUSTOMPROPERTY25,EMPLOYEETYPE,CUSTOMPROPERTY3,costcenter,EmployeeClass,enddate,startdate,CUSTOMPROPERTY13,Email,CUSTOMPROPERTY1,CUSTOMPROPERTY2,jobCode,jobdescription,Owner,orgunitid,CUSTOMPROPERTY9,CUSTOMPROPERTY11,CUSTOMPROPERTY12,City,Country,Region,TERMDATE

[sudeshjaiswal]

Hello @WilliamD,

Can you please remove the termdate from the sav file and only set it using PREPROCESSQUERY.

Thanks