Schedule automated message reminder gives exception

Hi

We have made a few template reminders for program stages and are trying to test them. Few months back we had tested the same and they were working. Now they are not.

On clicking the execute button no messages are sent and the tomcat log shows this error (I suspect this has to do with the change of program type field)-

* INFO 2015-08-28 14:44:14,852 Start to prepare reminder messages: 00:00:00.001 (Clock.java [taskScheduler-2])

* ERROR 2015-08-28 14:44:14,915 Unexpected error occurred in scheduled task. (TaskUtils.java [taskScheduler-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ ( SELECT pi.programinstanceid, pav.value as phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident , pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN trackedentityattributevalue pav ON pav.trackedentityinstanceid=p.trackedentityinstanceid INNER JOIN trackedentityattribute pa ON pa.trackedentityattributeid=pav.trackedentityattributeid WHERE pi.status= 0 and prm.templatemessage is not NULL and prm.templatemessage != ‘’ and pg.type=1 and prm.daysallowedsendmessage is not null and pa.valuetype=‘phoneNumber’ and ( DATE(now()) - DATE(pi.enrollmentdate) ) = prm.daysallowedsendmessage and prm.whenToSend is null and prm.dateToCompare=‘enrollmentdate’ and prm.sendto = 1 ) UNION ( SELECT pi.programinstanceid, org.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid WHERE pi.status = 0 and org.phonenumber is not NULL and org.phonenumber != ‘’ and prm.templatemessage is not NULL and prm.templatemessage != ‘’ and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( pi.enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare=‘enrollmentdate’ and prm.whenToSend is null and prm.sendto = 3 ) UNION ( SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident ,pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN usermembership ums ON ums.organisationunitid = p.organisationunitid INNER JOIN userinfo uif ON uif.userinfoid = ums.userinfoid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != ‘’ and prm.templatemessage is not NULL and prm.templatemessage != ‘’ and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare=‘enrollmentdate’ and prm.sendto = 4 ) UNION ( select pi.programinstanceid, uif.phonenumber,prm.templatemessage, org.name as orgunitName , pg.name as programName, pi.dateofincident, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date from trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pg.programid INNER JOIN usergroupmembers ugm ON ugm.usergroupid = prm.usergroupid INNER JOIN userinfo uif ON uif.userinfoid = ugm.userid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != ‘’ and prm.templatemessage is not NULL and prm.templatemessage != ‘’ and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE(enrollmentdate) ) = prm.daysallowedsendmessage and prm.whentosend is null and prm.sendto = 5 ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer

Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Position: 1077

Kindly help.

regards

harsh