java.sql.SQLSyntaxErrorException: ORA-01797: this operator must be followed by ANY or ALL

My query is

            sql = "SELECT pmmr.REQUEST_NO ,  pmel.event_datetime Event_Datetime,Pmmr.Form_No Form_No, nvl(Pmf.Form_Name, Pmmr.Form_No) formName, pmmr.MRN, nvl  (p.FIRST_NAME || DECODE(p.FAMILY_NAME, NULL, '', ' ' || p.FAMILY_NAME),pmmr.MRN)  PATIENT_NAME,pmmr.ASSIGNED_TO,"
                    +"            pmmr.DRUG_GENERIC_NAME,pmmr.LAST_STATUS, nvl(initcap(( hr1.FIRST_NAME || ' ' ||  hr1.LAST_NAME)),pmmr.LAST_PERFORMER_ID) LastActionBy,"
                    +"            nvl(hr2.DEPARTMENT || ' - ' || hr2.SECTION_NAME,'') ORGANIZATION_UNIT, nvl(initcap(( hr2.FIRST_NAME || ' ' || hr2.LAST_NAME)),pmmr.REQUESTER_ID) RequesterName, pmmr.REQUEST_DATE,"
                    +"            pmmr.item_cost"
                    +"            FROM PHRM_MFRP_MEDICATION_REQUEST pmmr"
                    +"            join PHRM_MFRP_EVENT_LOG pmel on pmmr.REQUEST_NO = pmel.REQUEST_NO"
                    +"            left outer join Hr_Employee hr1 on Pmmr.Last_Performer_Id = Hr1.Employee_Number"
                    +"            left outer join Hr_Employee hr2 on Pmmr.Requester_Id = Hr2.Employee_Number"
                    +"            left outer join EAPPTMT.PATIENT p on Pmmr.Mrn = P.Mrn"
                    +"            left outer join Phrm_Mfrp_Form pmf on Pmmr.Form_No = Pmf.Form_No";
            
        if (status.equals("Pending")){
} 
else if(status.equals("Approved")){
                sql += " WHERE  pmmr.BRANCH_ID = ?  AND pmel.EVENT_STATUS IN ('Pharmacy Director Approved')"
                        +" and Pmmr.Form_No = 1";
                sql +=" and pmel.event_datetime >= (?,'dd/MM/yyyy') and pmel.event_datetime <=   (?,'dd/MM/yyyy')";     
                sql+=" order by LAST_STATUS, EVENT_DATETIME DESC ";
                
            } else if(status.equals("Disapproved")){
                sql += " WHERE  pmmr.BRANCH_ID = ? AND pmel.EVENT_STATUS IN ('Pharmacy Director Disapproved')"
                        + "and Pmmr.Form_No = 1";
                sql +=" and pmel.event_datetime >= (?,'dd/MM/yyyy') and pmel.event_datetime <=   (?,'dd/MM/yyyy')";     
                sql+=" order by LAST_STATUS, EVENT_DATETIME DESC ";}
            stmt = Con.prepareStatement(sql);           
            stmt.setString(2, startDate);
            stmt.setString(3, endDate);
            //tmt.setString(3, status);
            stmt.setInt(1, branchId);
            rs = stmt.executeQuery();

I get the error java.sql.SQLSyntaxErrorException: ORA-01797: this operator must be followed by ANY or ALL whenever i run it,

What does it mean and how can I fix it ?

Answer

Your code is very messy and should be cleaned up. When asking the questions here you usually should create a MRE in which the concrete problem is highlighed, instead of pasting your whole code base here and letting us find your bugs.

Anyways since I got interested on this I spent a moment of googling and found your question already answered here.

It says the ORA-01797 is related to the query missing the to_date() constant.

From looking at your code I can see several places where it’s missing like the following one:

sql +=" and pmel.event_datetime >= /*to_date missing here!*/ (?,'dd/MM/yyyy') and
pmel.event_datetime <=  /*to_date missing here!*/ (?,'dd/MM/yyyy')";

Try to fix it and then it should work. Hope it’ll help you