Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
05/16/2024 06:43 AM
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 )"]}
05/16/2024 07:41 AM
@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" ] }
05/16/2024 10:31 AM
@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' |
05/16/2024 11:18 AM - edited 05/16/2024 11:18 AM
05/16/2024 06:34 PM
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.
05/17/2024 06:02 AM
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.
05/17/2024 07:58 AM - edited 05/17/2024 07:59 AM
@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.
05/19/2024 10:39 AM
Share input record details masking sensitive info
05/16/2024 07:50 AM - edited 05/16/2024 07:55 AM
@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"
]
}