Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
10/13/2023 08:13 AM - edited 10/13/2023 08:14 AM
Hi,
We are looking to set customproperty45 to different values to trigger certain user update rules for relevant actions upon import from Workday
This is the current syntax for the query:
"UPDATE NEWUSERDATA SET customproperty45 = CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"
**We've also tried this with the values in single quotes. In both scenarios, when customproperty45=1, the actual result has been null when the expected result is 2.
Any suggestions on how to fix this? It seems like the import is always seeing the value for customproperty45 as NULL.
The Json part for this query:
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY, customproperty45 FROM USERS" // tried this w/o including CP45 and "userkey" is needed for the other queries that we are calling
},
"COMPUTEDCOLUMNS": [
"customproperty45"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET customproperty45 = CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"
]
}
10/13/2023 01:38 PM
10/16/2023 04:33 PM
No. We are not importing anything from Workday for this customproperty.
10/17/2023 11:32 AM
Then it will be always null because from import you are not getting CP45 so newuser data will always have null for CP45 and won't get updated as your case statement is also not matching.
What is is your actual use case and what is that you want achieve?
10/14/2023 06:14 PM - edited 10/14/2023 06:14 PM
Please try below
{ "ADDITIONALTABLES": { "USERS": "SELECT username , USERKEY, CUSTOMPROPERTY45 FROM USERS"},
"COMPUTEDCOLUMNS": [ "CUSTOMPROPERTY45"],
"PREPROCESSQUERIES": [
"ALTER TABLE NEWUSERDATA ADD INDEX `idx_USERNAME` (`USERNAME` ASC)",
"ALTER TABLE NEWUSERDATA ADD INDEX `idx_CP45` (`CUSTOMPROPERTY45` ASC)",
"UPDATE NEWUSERDATA NU1 INNER JOIN CURRENTUSERS CU1 ON CU1.USERNAME=NU1.USERNAME SET NU1.CUSTOMPROPERTY45 = CASE WHEN customproperty45=1 THEN 2 WHEN customproperty45=2 THEN 3 END"
] }
10/16/2023 04:33 PM
I got the following error when I tried the above in the job log for failure message - Error in Users Import - Error while processing data: BLOB/TEXT column 'CUSTOMPROPERTY45' used in key specification without a key length
10/17/2023 09:10 PM
Use any customproperty with varchar(255) datatype such as CP19
10/17/2023 10:32 PM
Is there a list somewhere of custom properties and their data types?
We are already using the following for different purposes: 1 to 24, 39, 42, 43, 44, 46, 47, 49, 50, 61, 65
10/18/2023 06:54 AM
10/19/2023 08:33 AM
I tried it with customproperty38 which is of varchar(255) type and got the following error -
Error in Users Import - Error while processing data: Column 'customproperty38' in field list is ambiguous
10/23/2023 04:18 PM
Hello, any suggestion on how to proceed on this? Not sure why we are seeing this error.
10/23/2023 06:56 PM
use alias
10/23/2023 04:44 PM
Modifying the Update statement to make sure its looking for the custom property on the current user worked. Highlighted the change.
UPDATE NEWUSERDATA NU1 INNER JOIN CURRENTUSERS CU1 ON CU1.USERNAME=NU1.USERNAME SET NU1.CUSTOMPROPERTY38 = CASE WHEN cu1.customproperty38=1 THEN 2 WHEN cu1.customproperty38=2 THEN 3 END"