if condition in preprocessor query

[Abdul_Gaffar]

Hi ,

We are trying to include if condition in preprocessor query as follows:

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20", "STATUSKEY", "DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='Workday'",
"UPDATE NEWUSERDATA SET STATUSKEY='1' IF(JOBCODEDESC='A') else SET STATUSKEY='0'",
"UPDATE NEWUSERDATA SET displayName = CONCAT(FirstName,' ',LastName) where (displayName is null or displayName='')"
]
}

However, after  running the import job it fails and gives error as follows

Error in Users Import - Error while processing data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (JOBCODEDESC='A') else SET STATUSKEY='0'' at line 1

please suggest what shall be used for implementing condition for the preprocessor query

Reagrds,

Abdul Gaffar

[SB · Accepted solution]

You can use the below format

STATUSKEY= IF(condition, value_if_true, value_if_false)

UPDATE NEWUSERDATA SET STATUSKEY=IF(JOBCODEDESC='A', 1, 0)

[dgandhi · Accepted solution]

Its syntax error , please try below and it should work.

UPDATE NEWUSERDATA SET STATUSKEY = (case when JOBCODEDESC='A' then '1' else '0' end );

Thanks