User import preprocessor syntax, > character

[Community_User]

Originally posted on September 9 2020 at 10:32 UTC

What kind of syntax should be used for user import preprocessor when comparing dates? I am getting the following error when using the preprocessor to calculate statuskey, how "greater than" character > can be used in the query? I have tried using different ways to escape the character but have not yet found anything to be working.

Configuration:

{"ADDITIONALTABLES":{"USERS":"SELECT USERKEY FROM USERS"},"COMPUTEDCOLUMNS":["systemusername","employeeType","statuskey"],"PREPROCESSQUERIES":

[

"UPDATE NEWUSERDATA SET systemusername = LEFT(email,LOCATE('@',email) - 1)",

"UPDATE NEWUSERDATA SET employeeType = 'Employee'",

"UPDATE NEWUSERDATA SET statuskey = IF(NOW()>(enddate),'0','1')"

]}

Error:

2020-09-03 13:43:04,932 [https-jsse-nio-443-exec-66] DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_14153 SET statuskey = IF(NOW()>(enddate),'0','1')

2020-09-03 13:43:04,936 [https-jsse-nio-443-exec-66] ERROR services.ImportSAvDataUserService - Error while processing data:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ';(enddate),'0','1')' at line 1

[Community_User]

Originally posted on September 9 2020 at 10:57 UTC

HI Mikko,

You need to use case statements in above SQL.

Below an example:

"UPDATE NEWUSERDATA SET statuskey = CASE WHEN CURDATE()>ENDDATE THEN 1 ELSE 0"

It will set the value of statuskey as 1 if currendate is greater than endate else, 0.

[Community_User]

Originally posted on September 9 2020 at 10:58 UTC

HI Mikko,

You need to use case statements in above SQL.

Below an example:

"UPDATE NEWUSERDATA SET statuskey = CASE WHEN CURDATE()>ENDDATE THEN 1 ELSE 0"

It will set the value of statuskey as 1 if currendate is greater than endate else, 0.

[Community_User]

Originally posted on September 9 2020 at 11:39 UTC

Thanks for the reply, however I'm still running into the same issue of greater than character not being processed correctly:

2020-09-09 11:35:06,645 [https-jsse-nio-443-exec-27] DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_14291 SET statuskey = CASE WHEN CURDATE()>ENDDATE THEN 1 ELSE 0 END

2020-09-09 11:35:06,649 [https-jsse-nio-443-exec-27] ERROR services.ImportSAvDataUserService - Error while processing data:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ';ENDDATE THEN 1 ELSE 0 END' at line 1

[Community_User]

Originally posted on September 9 2020 at 11:42 UTC

"UPDATE NEWUSERDATA SET statuskey = CASE WHEN CURDATE() > ENDDATE THEN 1 ELSE 0 END"

is working for me.

[Community_User · Accepted solution]

Originally posted on September 9 2020 at 11:55 UTC

Thanks, it seems that spaces are needed and then it is processed correctly.