PreProcessor Query to update status giving error

[SanjeetaRao]

Hi,

Our requirement is to change status to Inactive for users whose

(a)CP35 is null and

(b)start date is 21 days or more from current date.

We are using the below preprocessor but the job is failing.

{
"ADDITIONALTABLES": {
"USERS": "SELECT enddate,username,systemusername,startdate,customproperty35 FROM users"
},
"COMPUTEDCOLUMNS": [
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET statuskey =case when NEWUSERDATA.customproperty35 is null and (datediff(sysdate(),DATE_FORMAT(NEWUSERDATA.startdate,'%Y-%m-%d %H:%i:%s')) >=21) then 'inactive' else 'active' end"
]
}

Is there some modification required in the preprocessor. Please suggest.

[NM]

@SanjeetaRao try this

{

"ADDITIONALTABLES": {

"USERS": "SELECT enddate,username,systemusername,startdate,customproperty35 FROM users"

},

"COMPUTEDCOLUMNS": [

"statuskey"

],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA SET statuskey =case when NEWUSERDATA.customproperty35 is null and (datediff(sysdate(),DATE_FORMAT(NEWUSERDATA.startdate,'%Y-%m-%d %H:%i:%s')) >=21) then 0 else 1 end"

If that doesn't work try giving 1 and 0 in single quotes

]

}

[SanjeetaRao]

Hi @NM 

We tried with 0,1 and '0','1'. We are getting the same error.

Error in Users Import - Error while processing data: Unknown column 'TEMPNEWUSERS_42793.customproperty35' in 'field list'.

Could you please help

[NM]

Try

case when customproprty35 instead of adding newuserdata at the start.

[SanjeetaRao]

Hi @NM ,

I ran the job after replacing NEWUSERDATA.customproperty35 with just customproperty35.

It gave the below error: 

Error in Users Import - Error while processing data: Unknown column 'customproperty35' in 'field list'

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT enddate, username, systemusername, startdate, customproperty35 FROM users"
  },
  "COMPUTEDCOLUMNS": [
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA SET statuskey = CASE 
      WHEN NEWUSERDATA.customproperty35 IS NULL 
      AND DATEDIFF(SYSDATE(), DATE_FORMAT(NEWUSERDATA.startdate, '%Y-%m-%d %H:%i:%s')) >= 21 
      THEN 0
      ELSE 1
    END"
  ]
}

[SanjeetaRao]

Hi @rushikeshvartak ,

We tried the above solution. But we are getting the same error we were getting earlier.

Error in Users Import - Error while processing data: Unknown column 'TEMPNEWUSERS_43934.customproperty35' in 'field list'

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT enddate, username, systemusername, startdate, customproperty35 FROM users"
  },
  "COMPUTEDCOLUMNS": [
    "statuskey",  "customproperty35"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA SET statuskey = CASE 
      WHEN NEWUSERDATA.customproperty35 IS NULL 
      AND DATEDIFF(SYSDATE(), DATE_FORMAT(NEWUSERDATA.startdate, '%Y-%m-%d %H:%i:%s')) >= 21 
      THEN 0
      ELSE 1
    END"
  ]
}