SAV file MODIFYUSERDATAJSON update

[IAM]

I have the below query in a custom query job. Instead, I need to move this to a SAV file to process instead of in the custom query job but I'm not sure how to do this.

Here is the SQL query:

update users inner join user_accounts on users.MANAGER = user_accounts.USERKEY and users.STATUSKEY=1 inner join accounts on user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY and accounts.endpointkey in (1) and accounts.status = '1' set users.CUSTOMPROPERTY1 = accounts.customproperty15

Here is what I have currently in my SAV file that I need to modify:

#MODIFYUSERDATAJSON={ "ADDITIONALTABLES" : { "USERS" : "SELECT USERKEY,USERNAME,CUSTOMPROPERTY5,STATUSKEY FROM USERS" }, "COMPUTEDCOLUMNS" : ["MANAGER","EMPLOYEEID","CUSTOMPROPERTY39","CUSTOMPROPERTY34"], "PREPROCESSQUERIES" : [ "UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5","UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME","UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'","UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = ( CASE WHEN (STATUSKEY = 'ACTIVE_ASSIGN') THEN 'ACTIVE' WHEN (STATUSKEY = 'SUSP_ASSIGN') THEN 'SUSPENDED' ELSE ('TERMED') END )"]}

[Saathvik]

@IAM : You can try below JSON, Also I see you are mapping MANGER and EMPLOYEEID with CUSTOMPROPERTY5 and USERNAME columns of user respectively using MODIFYUSERDATAJSON which can be mapped directly as part of import itself isn't it?

#MODIFYUSERDATAJSON={ "ADDITIONALTABLES": { "USERS": "SELECT USERKEY,USERNAME,MANAGER,CUSTOMPROPERTY1,CUSTOMPROPERTY5,STATUSKEY FROM USERS", "ACCOUNTS": "SELECT ACCOUNTKEY, NAME, ACCOUNTID, ENDPOINTKEY, CUSTOMPROPERTY15, STATUS FROM ACCOUNTS WHERE ENDPOINTKEY=1", "USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS" }, "COMPUTEDCOLUMNS": [ "MANAGER", "EMPLOYEEID", "CUSTOMPROPERTY39", "CUSTOMPROPERTY34", "CUSTOMPROPERTY1" ], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5", "UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME", "UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'", "UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = ( CASE WHEN (STATUSKEY = 'ACTIVE_ASSIGN') THEN 'ACTIVE' WHEN (STATUSKEY = 'SUSP_ASSIGN') THEN 'SUSPENDED' ELSE ('TERMED') END )", "UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSER_ACCOUNTS CCA on NU.MANAGER=CCA.USERKEY AND NU.STATUSKEY=1 INNER JOIN CURRENTACCOUNTS CA ON CCA.ACCOUNTKEY=CA.ACCOUNTKEY AND CA.ENDPOINTKEY=1 AND CA.STATUS=1 SET NU.CUSTOMPROPERTY1=CA.CUSTOMPROPERTY15" ] }

[IAM]

@Saathvik Thank you. This is the error I'm getting now

Error in Users Import - Error while processing
data: Data truncation: Truncated incorrect DOUBLE
value: 'ACTIVE_ASSIGN'

[Saathvik]

@IAM: ACTIVE_ASSIGN which attribute is having this value and to which user column it is mapped ?

[rushikeshvartak]

here was an attempt to insert a value into a column with a DOUBLE data type, but the value being inserted is not a valid DOUBLE. Specifically, the value 'ACTIVE_ASSIGN' cannot be converted to a DOUBLE because it is a string.

[IAM]

@rushikeshvartak @Saathvik 

This was working before. This is how the active/inactive status comes in from HR. We are just setting CP34 either to ACTIVE, SUSPENDED, or TERMED. This hasn't changed, the only thing I added was what Saathvik gave.

[Saathvik]

@IAM : Looks like you are assigning STATUSKEY column of user to a string which it doesn't support it only accepts 1 or 0. This I am assuming based on the existing logic you have in your MODIFYUSERDATAJSON.

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = (CASE WHEN STATUSKEY = 'ACTIVE_ASSIGN' THEN 'ACTIVE' WHEN STATUSKEY = 'SUSP_ASSIGN' THEN 'SUSPENDED' ELSE ('TERMED') END )",

Please correct you logic and try again.

[rushikeshvartak]

Share input record details masking sensitive info

[Raghu]

@IAM  try below

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,MANAGER,USERNAME, CUSTOMPROPERTY5, STATUSKEY FROM USERS",
"USER_ACCOUNTS": "SELECT USERKEY, ACCOUNTKEY FROM USER_ACCOUNTS",
"ACCOUNTS": "SELECT ACCOUNTKEY, CUSTOMPROPERTY15 FROM ACCOUNTS WHERE ENDPOINTKEY = 1 AND STATUS = '1'"
},
"COMPUTEDCOLUMNS": ["MANAGER", "EMPLOYEEID", "CUSTOMPROPERTY39", "CUSTOMPROPERTY34","CUSTOMPROPERTY1"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5",
"UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = (CASE WHEN STATUSKEY = 'ACTIVE_ASSIGN' THEN 'ACTIVE' WHEN STATUSKEY = 'SUSP_ASSIGN' THEN 'SUSPENDED' ELSE ('TERMED') END )",
"UPDATE NEWUSERDATA INNER JOIN USER_ACCOUNTS ON NEWUSERDATA.MANAGER = USER_ACCOUNTS.USERKEY AND NEWUSERDATA.STATUSKEY = 1 INNER JOIN ACCOUNTS ON USER_ACCOUNTS.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY SET NEWUSERDATA.CUSTOMPROPERTY1 = ACCOUNTS.CUSTOMPROPERTY15"
]
}