Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

MODIFYUSERDATAJSON- Giving Syntax exception while CONCAT

NID27
Regular Contributor
Regular Contributor

Hello,

We have automated userimport which stores address in "Street" and Zipcode in "CP44".

I want Street and Zipcode to be concatenated. Below is the existing modifyuserdata json which works. Can you please help me concat street and CP44 to CP49.

{ "ADDITIONALTABLES":

{ "USERS": "SELECT FIRSTNAME,username,customproperty36,CUSTOMPROPERTY53,CUSTOMPROPERTY54,EMPLOYEEID,userkey,customproperty65,lastname from USERS",
"CUSTOMER": "SELECT customerkey,CUSTOMPROPERTY1,CUSTOMERNAME FROM CUSTOMER",
"ACCOUNTS": "SELECT accountid,CUSTOMPROPERTY22,status FROM ACCOUNTS" },

"COMPUTEDCOLUMNS":
[
"PREFEREDFIRSTNAME",
"statuskey",
"ORGUNITID",
"FIRSTNAME",
"lastname",
"CUSTOMPROPERTY39",
"CUSTOMPROPERTY36",
"CUSTOMPROPERTY54",
"CUSTOMPROPERTY53",
"customproperty11",
"customproperty65",
"manager",
"OWNER"
],

"PREPROCESSQUERIES":
[
"UPDATE NEWUSERDATA set NEWUSERDATA.PREFEREDFIRSTNAME = NEWUSERDATA.FIRSTNAME WHERE NEWUSERDATA.PREFEREDFIRSTNAME IS NULL OR NEWUSERDATA.PREFEREDFIRSTNAME=''",

"UPDATE NEWUSERDATA set NEWUSERDATA.customproperty65 = NEWUSERDATA.lastname WHERE NEWUSERDATA.customproperty65 IS NULL OR NEWUSERDATA.customproperty65=''",

"UPDATE NEWUSERDATA AS NU SET NU.ORGUNITID = (select CC.CUSTOMPROPERTY1 from CURRENTCUSTOMER CC WHERE CC.CUSTOMERNAME=IFNULL(NU.CUSTOMPROPERTY36, 'Default'))", "UPDATE NEWUSERDATA as NU SET NU.CUSTOMPROPERTY39 =(select A.accountid from CURRENTACCOUNTS A WHERE A.CUSTOMPROPERTY22=NU.customproperty11 and status = '1')",

"UPDATE NEWUSERDATA as NU SET NU.statuskey=1 where NU.CUSTOMPROPERTY54 in ('ACTIVE', 'LOACFRA', 'LOAFMLA', 'LOAMILITARY', 'LOANYPFL', 'LOAPDL', 'LOAPERSONAL','LOAWORKERSCOMP') or (NU.CUSTOMPROPERTY54='PRESTART' and STR_TO_DATE(NU.customproperty53,'%Y-%m-%dT%H:%i:%s.000Z') <= DATE_ADD(STR_TO_DATE(CURRENT_DATE,'%Y-%m-%dT%H:%i:%s.000Z'),INTERVAL 14 DAY))",

"UPDATE NEWUSERDATA as NU SET NU.statuskey = 0 where NU.CUSTOMPROPERTY54='TERMINATED' or ( NU.CUSTOMPROPERTY54='PRESTART' and STR_TO_DATE(NU.customproperty53,'%Y-%m-%dT%H:%i:%s.000Z') > DATE_ADD(STR_TO_DATE(CURRENT_DATE,'%Y-%m-%dT%H:%i:%s.000Z'),INTERVAL 14 DAY))", "UPDATE NEWUSERDATA as NU SET NU.manager =(select username from currentusers cu where cu.employeeid=NU.customproperty11)",

"UPDATE NEWUSERDATA as NU SET NU.owner =(select username from currentusers cu where cu.employeeid=NU.customproperty11)" ] }

4 REPLIES 4

rushikeshvartak
All-Star
All-Star

 

 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT FIRSTNAME, username, customproperty36, CUSTOMPROPERTY53, CUSTOMPROPERTY54, EMPLOYEEID, userkey, customproperty65, lastname,CUSTOMPROPERTY44,Street from USERS",
    "CUSTOMER": "SELECT customerkey, CUSTOMPROPERTY1, CUSTOMERNAME FROM CUSTOMER",
    "ACCOUNTS": "SELECT accountid, CUSTOMPROPERTY22, status FROM ACCOUNTS"
  },
  "COMPUTEDCOLUMNS": [
    "PREFEREDFIRSTNAME",
    "statuskey",
    "ORGUNITID",
    "FIRSTNAME",
    "lastname",
    "CUSTOMPROPERTY39",
    "CUSTOMPROPERTY36",
    "CUSTOMPROPERTY49",
    "CUSTOMPROPERTY54",
    "CUSTOMPROPERTY53",
    "customproperty11",
    "customproperty65",
"street",
 "customproperty44",
    "manager",
    "OWNER"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA set NEWUSERDATA.PREFEREDFIRSTNAME = NEWUSERDATA.FIRSTNAME WHERE NEWUSERDATA.PREFEREDFIRSTNAME IS NULL OR NEWUSERDATA.PREFEREDFIRSTNAME=''",
    "UPDATE NEWUSERDATA set NEWUSERDATA.customproperty65 = NEWUSERDATA.lastname WHERE NEWUSERDATA.customproperty65 IS NULL OR NEWUSERDATA.customproperty65=''",
    "UPDATE NEWUSERDATA AS NU SET NU.ORGUNITID = (select CC.CUSTOMPROPERTY1 from CURRENTCUSTOMER CC WHERE CC.CUSTOMERNAME=IFNULL(NU.CUSTOMPROPERTY36, 'Default'))",
    "UPDATE NEWUSERDATA as NU SET NU.CUSTOMPROPERTY39 =(select A.accountid from CURRENTACCOUNTS A WHERE A.CUSTOMPROPERTY22=NU.customproperty11 and status = '1')",
    "UPDATE NEWUSERDATA as NU SET NU.statuskey=1 where NU.CUSTOMPROPERTY54 in ('ACTIVE', 'LOACFRA', 'LOAFMLA', 'LOAMILITARY', 'LOANYPFL', 'LOAPDL', 'LOAPERSONAL','LOAWORKERSCOMP') or (NU.CUSTOMPROPERTY54='PRESTART' and STR_TO_DATE(NU.customproperty53,'%Y-%m-%dT%H:%i:%s.000Z') <= DATE_ADD(STR_TO_DATE(CURRENT_DATE,'%Y-%m-%dT%H:%i:%s.000Z'),INTERVAL 14 DAY))",
    "UPDATE NEWUSERDATA as NU SET NU.statuskey = 0 where NU.CUSTOMPROPERTY54='TERMINATED' or ( NU.CUSTOMPROPERTY54='PRESTART' and STR_TO_DATE(NU.customproperty53,'%Y-%m-%dT%H:%i:%s.000Z') > DATE_ADD(STR_TO_DATE(CURRENT_DATE,'%Y-%m-%dT%H:%i:%s.000Z'),INTERVAL 14 DAY))",
    "UPDATE NEWUSERDATA as NU SET NU.manager =(select username from currentusers cu where cu.employeeid=NU.customproperty11)",
    "UPDATE NEWUSERDATA as NU SET NU.owner =(select username from currentusers cu where cu.employeeid=NU.customproperty11)",
    "UPDATE NEWUSERDATA as NU SET NU.customproperty49 = CONCAT(NU.Street, ' ', NU.customproperty44)"
  ]
}

 

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

NID27
Regular Contributor
Regular Contributor

Hi Rushi,

I get below error while importing users after updating Modifyuserjson.

Error in Users Import - Error while processing data: Unknown column 'NU.Street' in 'field list'

NID27_0-1716976972613.png

Thanks

Nidhi

NID27
Regular Contributor
Regular Contributor

The job is now sucessful.

Solution:

Added: Street and CUSTOMPROPERTY44 under "COMPUTED COLUMNS"

Updated thanks


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.