Syntax to UPDATE DB Column based on Entitlement value of GRANTACCESSJSON

[harishyara]

Hello All,

We have a requirement to update users department column based on entitlement value.

In GRANTACCESSJSON of DB connection using below syntax but it is giving error

{
"Role" : [
"Update DBNAME.TABLENAME Set USERDEPT=CASE WHEN ${task.entitlement_valueKey.entitlement_value=='ROLE_USER'} THEN '' ELSE ${task.entitlement_valueKey.entitlement_value} END where USERID = '${task.accountName}'"
]
}

Error message - [SQL0199] Keyword THEN not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT.

Please help me to fix this issue.

Thank you

[armaanzahir]

Hi @harishyara 

Can you try the below code and check if it works:

{
"Role" : [
"Update DBNAME.TABLENAME Set USERDEPT=CASE WHEN ${task.entitlement_valueKey.entitlement_value}='ROLE_USER' THEN '' ELSE ${task.entitlement_valueKey.entitlement_value} END where USERID = '${task.accountName}'"
]
}

[harishyara]

Hello @armaanzahir I tried with your syntax still throwing same error.

[sk]

@harishyara : Try below logic

{
"Role" : [
"Update DBNAME.TABLENAME Set USERDEPT=NULLIF(${task.entitlement_valueKey.entitlement_value},'ROLE_USER') where USERID = '${task.accountName}'"
]
}