schema user job is failing due to syntax error

[Sreejith]

Hi Team,

We importing user through schema user import job.

we are also having a sav file for the schema import when am running the job am getting error.

so i go through and found only 1 condition its failing when i removed and tried that time its working.

but i needs to put this logic in preprocessor query.

am attaching the error and logic below.

kindly help me identify the issue.

Failure msg : 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 ',CUSTOMPROPERTY39 VARCHAR(255) DEFAULT NULL ) ENGINE=In

Logic i give in the preprocessor : 

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME=CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty39 = (CASE WHEN (NEWUSERDATA.customproperty6 = 'X' AND NEWUSERDATA.customproperty39 = '' AND CURRENTUSERS.customproperty39 != '') THEN CURRENTUSERS.customproperty39 ELSE NEWUSERDATA.customproperty39 END)"

Note : (CP39 is getting contractor enddate value)

[PremMahadikar]

Hi @Sreejith ,

Can you try replacing  CURRENTUSERS.customproperty39 != '' with  CURRENTUSERS.customproperty39 != null

If this helps your question, please consider selecting Accept As Solution and hit Kudos

[Sreejith]

@PremMahadikar did you mean only for currentusers or also needs to change newuserdata as  NEWUSERDATA.customproperty39 = null ?

[PremMahadikar]

@Sreejith ,

Only CURRENTUSERS.customproperty39 != null

[Raghu]

@Sreejith  try below:

"UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS
ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME
SET NEWUSERDATA.customproperty39 = (
CASE
WHEN (
NEWUSERDATA.customproperty6 = 'X'
AND (NEWUSERDATA.customproperty39 IS NULL OR NEWUSERDATA.customproperty39 = '')
AND CURRENTUSERS.customproperty39 IS NOT NULL
AND CURRENTUSERS.customproperty39 != ''
)
THEN CURRENTUSERS.customproperty39
ELSE NEWUSERDATA.customproperty39
END
)"

[rushikeshvartak]

UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME
SET NEWUSERDATA.customproperty39 = CASE
WHEN (NEWUSERDATA.customproperty6 = 'X'
AND NEWUSERDATA.customproperty39 = ''
AND CURRENTUSERS.customproperty39 != '')
THEN CURRENTUSERS.customproperty39
ELSE NEWUSERDATA.customproperty39
END;