05/19/2023 12:45 AM
Hi all,
Is it possible to add more than 2 update queries in the UPDATEACCOUNTJSON block of the DB connector?
The requirement is that once the user value is updated we need to update 3 tables in target.
Is it possible in the connection?
Regards,
Gopi.
05/19/2023 04:03 AM
Hello @Gopi,
Unfortunately, it is not feasible to employ two update queries within the UpdateAccountJSON in the DB Connector.
Regards,
05/19/2023 04:13 AM
05/19/2023 05:00 AM
Hello @Gopi,
You May Try using UPDATEUSERJSON, The UPDATEUSERJSON parameter in the Database connector executes each comma-separated query in the JSON value as an individual statement.
Sample Query:
{ "updateUserQry":[
"update users set customproperty5 = case when customproperty5 = 'Terminate' then '-2' when customproperty5 = 'Active' then '01' when customproperty5 = 'LOA' then '13' when customproperty5 = 'Suspend' then '04' when customproperty5 = 'Severance' then '05' else customproperty5 end where userkey = ${user.id} ",
"update users set termdate = null where userkey = ${user.id} and termdate is not null and customproperty5 in (1,01,4, 04,13)",
"update users set termdate = curdate() where userkey = ${user.id} and termdate is null and customproperty5 in (-2,5)"
]
}
Thanks,
05/19/2023 05:12 AM
If you separate query by comma are you getting any error
05/19/2023 05:18 AM
Hi @rushikeshvartak ,
yes, I'm getting an error.
Code: {"UpdateAccountQry" : ["UPDATE SYSADM.PS_NOVL_NDS_DATA SET DESCR='MYITACCESS' WHERE emplid='${user.employeeid}'","UPDATE SYSADM.PS_DIRXML_STAGE01 SET NET_RETURN_STATUS='MYITACCESS' WHERE emplid='${user.employeeid}'"]}
Screenshot:
Regards,
Gopi.
05/19/2023 06:04 AM
You can join both queries if possible into single query or create stored procedure
05/23/2023 07:08 AM
Hi all,
It's working fine for Updating multiple tables in USERUPDATEJSON. Thanks for all your help.
One more thing I need to check: If a user record exists in that table I'm good to update if not I need to insert data. How can I check this in the connection?
Regards,
Gopi.
06/09/2023 05:43 AM
Hello @Gopi,
In Connection params, you cannot pass these details.
Thanks