Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/29/2024 03:37 AM
Solved! Go to Solution.
04/29/2024 03:42 AM
@TheSaviyntBoy try below
{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY1,PREFEREDFIRSTNAME,LASTNAME,FIRSTNAME,DISPLAYNAME FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.DISPLAYNAME = (CASE WHEN (NEWUSERDATA.PREFEREDFIRSTNAME != '' OR NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL) THEN (CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME)) ELSE (CONCAT(NEWUSERDATA.DEPARTMENTNAME, '.', NEWUSERDATA.LASTNAME)) END);"
]
}
04/29/2024 03:48 AM
Yeah, I already tried and the result is the same, nothing is coming before the '.' in the ELSE statement.
04/29/2024 06:11 AM
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.DISPLAYNAME = (CASE WHEN (NEWUSERDATA.PREFEREDFIRSTNAME != '' AND NEWUSERDATA.PREFEREDFIRSTNAME IS NOT NULL) THEN CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME) ELSE CONCAT(NEWUSERDATA.PREFEREDFIRSTNAME, '.', NEWUSERDATA.LASTNAME) END)"
]
}
04/29/2024 06:26 AM
Hi @TheSaviyntBoy ,
The below is working, please try. Make sure columns mentioned in the 'NEWUSERDATA' are also in your csv file.
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY1",
"PREFEREDFIRSTNAME",
"LASTNAME",
"FIRSTNAME",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.displayname = CASE WHEN (NU.preferedfirstname!= '' or NU.preferedfirstname!= null) THEN CONCAT(NU.preferedfirstname,'.',NU.lastname) ELSE CONCAT(NU.firstname,'.',NU.lastname) END"
]
}
If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos