How to pass the EndDate in UpdateaccountJSON(DB Connector)

[AravindK]

Hi Team,
We have requirement is when the user enddate is null then need to pass the null and if the user enddate has some value then we need to pass the value. 
Below are the two update queires which target system is expecting but we are unable to achieve this. 
Please help us with me syntax.

if End Date is NULL :

  "updateaccountqry": 

  "update AdImportedPrincipal set Email='murat.gurbuzer@tr.abb.com' ,LASTNAME = 'GURBUZER',firstname = 'PASA MURAT',Status = '1',COMPANY = 'ABB Power Grids Turkey Testing',COUNTRY = 'TurkiyedNew',TITLE = 'CHANNEL SALES MANAGER New',AbbDivShortName = 'PG',AbbBaShortName = 'PGGA',PRODUCTGROUPCODE = '2875',DISPLAYNAME = 'PASA MURAT GURBUZER',Department = 'PG,PGGA,2875',enddate = null where Identifier = '71697503'"

If End Date has some value :w

"updateaccountqry": 

  "update AdImportedPrincipal set Email='murat.gurbuzer@tr.abb.com' ,LASTNAME = 'GURBUZER',firstname = 'PASA MURAT',Status = '1',COMPANY = 'ABB Power Grids Turkey Testing',COUNTRY = 'TurkiyedNew',TITLE = 'CHANNEL SALES MANAGER New',AbbDivShortName = 'PG',AbbBaShortName = 'PGGA',PRODUCTGROUPCODE = '2875',DISPLAYNAME = 'PASA MURAT GURBUZER',Department = 'PG,PGGA,2875',enddate = '2023-04-20 00:00:00.0' where Identifier = '71697503'"

Update Account JSON is :

{

  "updateaccountqry":

  "update AdImportedPrincipal set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',AbbDivShortName = 'PG',AbbBaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = ${null!=user.enddate?'${user.enddate}':null} where Identifier = '${user.employeeid}'"

}

what should be the syntax if I want above output ??

Thanks,
Aravind

[Darshanjain]

Hi @AravindK 

can you try the COALESCE for enddate like below

enddate=COALESCE('${user.enddate}', null)

Thanks

Darshan

[IAM-VP]

Hi @Darshanjain ,

With the above solution i can see in the logs enddate = COALESCE('2023-04-20 00:00:00.0', null) , and target expecting this enddate ='2023-04-20 00:00:00.0'

[Darshanjain]

Hi @IAM-VP 

It means that target is not accepting null value in enddate, you can give a constant date and try .

Thanks

Darshan