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

UpdateUserJson in DB connector

saiKrishna
Regular Contributor
Regular Contributor

Hello Experts,

We have a requirement to add case statement for the below query

Where the requirement is if cp10 is updated as re-hired then comments should be re-hired and reason should be rehired 

if cp10 is updated as terminated or disabled then comments should be terminated and reason should be terminated

{
"UpdateUserQry": ["insert into [kh_abc].[db].[USER] (\"employee_number\",\"reason\", \"comments\",\"date_created\") values ('${user.username}','Re-hired','${user.enddate}','Re-hired',CURRENT_TIMESTAMP)"
]
}

Any advises o this

2 REPLIES 2

vivek9237
Regular Contributor
Regular Contributor

@saiKrishna Instead of hardcoding the value in the query, you have to write a groovy script similar to this - 

${user.customproperty10=='re-hired' ? 'rehired' : (user.customproperty10=='terminated' ? 'terminated' : '')}

Example: 

{
    "UpdateUserQry": [
        "insert into [kh_abc].[db].[USER] (\"employee_number\",\"reason\", \"comments\",\"date_created\") values ('${user.username}','${user.customproperty10=='re-hired' ? 'rehired' : (user.customproperty10=='terminated' ? 'terminated' : '')}','${user.customproperty10=='re-hired' ? 'rehired' : (user.customproperty10=='terminated' ? 'terminated' : '')}','${user.enddate}'"
    ]
}
Regards,

Vivek Mohanty


If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

rushikeshvartak
All-Star
All-Star

{
"UpdateUserQry": ["insert into [kh_abc].[db].[USER] (\"employee_number\", \"reason\", \"comments\", \"date_created\") values ('${user.username}', CASE WHEN '${user.customproperty10}' = 're-hired' THEN 'Re-hired' WHEN '${user.customproperty10}' IN ('terminated', 'disabled') THEN 'Terminated' ELSE 'Unknown' END, CASE WHEN '${user.customproperty10}' = 're-hired' THEN 'Re-hired' WHEN '${user.customproperty10}' IN ('terminated', 'disabled') THEN 'Terminated' ELSE 'Unknown' END, CURRENT_TIMESTAMP)"
]
}


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