Attribute gets overriden

[Kerasit]

Hi.

I have one connector. It imports to customproperty60.

I have another connector. It imports to enddate. However the second connector has a preprocc query:

{ 
	"ADDITIONALTABLES": { 
		"USERS": "SELECT firstname,middlename,startdate,location,customproperty6,customproperty40,employeetype,costcenter,enddate,customproperty60 FROM USERS"
	}, 
	"COMPUTEDCOLUMNS": [ 
		"firstname", "middlename", "displayname", "customproperty6", "costcenter", "customproperty11", "enddate", "customproperty60"
	], 
	"PREPROCESSQUERIES": [ 
		"DELETE FROM NEWUSERDATA WHERE EMPLOYEETYPE is null or EMPLOYEETYPE = ''",
		"UPDATE NEWUSERDATA SET MIDDLENAME = TRIM(SUBSTR(firstname,LOCATE(' ',firstname)))",
		"UPDATE NEWUSERDATA SET ENDDATE = case when enddate = customproperty60 then enddate when enddate = '' and customproperty60 != '' then customproperty60 when enddate != '' and customproperty60 = '' then enddate when enddate != '' and customproperty60 != '' and DATE(enddate) > DATE(customproperty60) then customproperty60 when enddate != '' and customproperty60 != '' and DATE(enddate) < DATE(customproperty60) then enddate else '' end",
		"UPDATE NEWUSERDATA SET FIRSTNAME = SUBSTRING_INDEX(firstname, ' ', 1)",
		"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(firstname,' ', lastname)", 
		"UPDATE NEWUSERDATA SET CUSTOMPROPERTY6 = case when location = 'DE' then 'Germany' when location = 'DK' then 'Denmark' when location = 'UK' then 'United Kingdom' when location = 'LU' then 'Luxembourg' when location = 'SG' then 'Singapore' when location = 'NL' then 'Holland' else location end", 
		"UPDATE NEWUSERDATA SET CUSTOMPROPERTY11 = case when employeetype = 'External Consultant' then 'OU=External Users,' when location = 'DK' then 'OU=Hellerup,' when location = 'UK' then 'OU=London,' when location = 'NL' then 'OU=Amsterdam,' when location = 'LU' then 'OU=Luxembourg,' when location = 'DE' then 'OU=Munich,' when location = 'SG' then 'OU=Singapore,' else '' end",
		"DELETE FROM NEWUSERDATA WHERE COSTCENTER = '9999'"
	] 
}	

If I runs the job for executing the first connector, customproperty60 gets a value, where there IS a value from the endpoint in the format: yyyy-mm-dd. I verifies this with the data analyzer.
When I then executes the job for the other connector (the one with the above MODIFYUSERDATAJSON), it removes all values from customproperty60. I have verified that it does NOT map any endpoint attribute to customproperty60.

I have no clue why this happens.

[Kerasit]

I have updated it with this to allign it with documentation:

{ 
	"ADDITIONALTABLES": { 
		"USERS": "SELECT firstname,middlename,startdate,location,customproperty6,customproperty40,employeetype,costcenter,enddate,customproperty60 FROM USERS"
	}, 
	"COMPUTEDCOLUMNS": [ 
		"firstname", "middlename", "displayname", "customproperty6", "costcenter", "customproperty11", "enddate"
	], 
	"PREPROCESSQUERIES": [ 
		"DELETE FROM NEWUSERDATA WHERE EMPLOYEETYPE is null or EMPLOYEETYPE = ''",
		"UPDATE NEWUSERDATA SET MIDDLENAME = TRIM(SUBSTR(firstname,LOCATE(' ',firstname)))",
		"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.ENDDATE = case when NEWUSERDATA.ENDDATE is not NULL and NEWUSERDATA.ENDDATE != '' and (CURRENTUSERS.CUSTOMPROPERTY60 is NULL or CURRENTUSERS.CUSTOMPROPERTY60='') THEN NEWUSERDATA.ENDDATE when CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '' and (NEWUSERDATA.ENDDATE is NULL or NEWUSERDATA.ENDDATE = '') then DATE(CURRENTUSERS.CUSTOMPROPERTY60) when CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '' and DATE(NEWUSERDATA.ENDDATE) > DATE(CURRENTUSERS.CUSTOMPROPERTY60) then DATE(CURRENTUSERS.CUSTOMPROPERTY60) when (CURRENTUSERS.CUSTOMPROPERTY60 is not NULL and CURRENTUSERS.CUSTOMPROPERTY60 != '') and (DATE(NEWUSERDATA.ENDDATE) < DATE(CURRENTUSERS.CUSTOMPROPERTY60)) then NEWUSERDATA.ENDDATE when (NEWUSERDATA.ENDDATE is NULL or NEWUSERDATA.ENDDATE = '') and (CURRENTUSERS.CUSTOMPROPERTY60 is NULL or CURRENTUSERS.CUSTOMPROPERTY60 = '') then '' else NULL end",
		"UPDATE NEWUSERDATA SET FIRSTNAME = SUBSTRING_INDEX(firstname, ' ', 1)",
		"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(firstname,' ', lastname)", 
		"UPDATE NEWUSERDATA SET CUSTOMPROPERTY6 = case when location = 'DE' then 'Germany' when location = 'DK' then 'Denmark' when location = 'UK' then 'United Kingdom' when location = 'LU' then 'Luxembourg' when location = 'SG' then 'Singapore' when location = 'NL' then 'Holland' else location end", 
		"UPDATE NEWUSERDATA SET CUSTOMPROPERTY11 = case when employeetype = 'External Consultant' then 'OU=External Users,' when location = 'DK' then 'OU=Hellerup,' when location = 'UK' then 'OU=London,' when location = 'NL' then 'OU=Amsterdam,' when location = 'LU' then 'OU=Luxembourg,' when location = 'DE' then 'OU=Munich,' when location = 'SG' then 'OU=Singapore,' else '' end",
		"DELETE FROM NEWUSERDATA WHERE COSTCENTER = '9999'", 
		"DELETE FROM NEWUSERDATA WHERE ENDDATE !='' AND ENDDATE <= DATE_SUB(now(),INTERVAL 1092 DAY)"
	] 
}	

However I am getting a SQL syntax error. I cannot find it. 
MySQL server version for the
right syntax to use near 'THEN
TEMPNEWUSERS_99357.ENDDATE when
TEMPUSERS_99357.CUSTOMPROPERTY60 is not NUL' at

I cannot see where this syntax error is. 😞

[rushikeshvartak · Accepted solution]

{ 
	"ADDITIONALTABLES": { 
		"USERS": "SELECT firstname,middlename,startdate,location,customproperty6,customproperty40,employeetype,costcenter,enddate,customproperty60 FROM USERS"
	}, 
	"COMPUTEDCOLUMNS": [ 
		"firstname", "middlename", "displayname", "customproperty6", "costcenter", "customproperty11", "enddate","employeetype"
	], 
	"PREPROCESSQUERIES": [ 
		"DELETE FROM NEWUSERDATA WHERE employeetype is null or employeetype = ''",
		"UPDATE NEWUSERDATA SET middlename = TRIM(SUBSTR(firstname,LOCATE(' ',firstname)))",
		"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.username = CURRENTUSERS.username SET NEWUSERDATA.enddate = case when NEWUSERDATA.enddate is not NULL and NEWUSERDATA.enddate != '' and (CURRENTUSERS.customproperty60 is NULL or CURRENTUSERS.customproperty60='') THEN NEWUSERDATA.enddate when CURRENTUSERS.customproperty60 is not NULL and CURRENTUSERS.customproperty60 != '' and (NEWUSERDATA.enddate is NULL or NEWUSERDATA.enddate = '') then DATE(CURRENTUSERS.customproperty60) when CURRENTUSERS.customproperty60 is not NULL and CURRENTUSERS.customproperty60 != '' and DATE(NEWUSERDATA.enddate) > DATE(CURRENTUSERS.customproperty60) then DATE(CURRENTUSERS.customproperty60) when (CURRENTUSERS.customproperty60 is not NULL and CURRENTUSERS.customproperty60 != '') and (DATE(NEWUSERDATA.enddate) < DATE(CURRENTUSERS.customproperty60)) then NEWUSERDATA.enddate when (NEWUSERDATA.enddate is NULL or NEWUSERDATA.enddate = '') and (CURRENTUSERS.customproperty60 is NULL or CURRENTUSERS.customproperty60 = '') then '' else NULL end",
		"UPDATE NEWUSERDATA SET firstname = SUBSTRING_INDEX(firstname, ' ', 1)",
		"UPDATE NEWUSERDATA SET displayname = CONCAT(firstname,' ', lastname)", 
		"UPDATE NEWUSERDATA SET customproperty6 = case when location = 'DE' then 'Germany' when location = 'DK' then 'Denmark' when location = 'UK' then 'United Kingdom' when location = 'LU' then 'Luxembourg' when location = 'SG' then 'Singapore' when location = 'NL' then 'Holland' else location end", 
		"UPDATE NEWUSERDATA SET customproperty11 = case when employeetype = 'External Consultant' then 'OU=External Users,' when location = 'DK' then 'OU=Hellerup,' when location = 'UK' then 'OU=London,' when location = 'NL' then 'OU=Amsterdam,' when location = 'LU' then 'OU=Luxembourg,' when location = 'DE' then 'OU=Munich,' when location = 'SG' then 'OU=Singapore,' else '' end",
		"DELETE FROM NEWUSERDATA WHERE costcenter = '9999'", 
		"DELETE FROM NEWUSERDATA WHERE enddate !='' AND enddate <= DATE_SUB(now(),INTERVAL 1092 DAY)"
	] 
}