Spring Data JPA- ignore parameter if it has a null value in between condition

I’m trying to write the following query

@Query(value = " " + "SELECT new om.gov.moh.ereferral.model.dto.ClinicalReferralStatisticsDto("
            + "SUM(CASE WHEN B.id = 1 THEN 1 END) AS OPD," + "SUM(CASE WHEN  B.id = 2 THEN 1 END)     AS IPD,"
            + "SUM(CASE WHEN B.id = 3 THEN 1 END)     AS DC," + "SUM(CASE WHEN B.id = 4 THEN 1 END )    AS PROC,"
            + " SUM(CASE WHEN B.id = 5 THEN 1 END)     AS SURGERY , SUM (CASE WHEN B.id IN (1,2,3,4,5)THEN 1 END)     AS Total)"
            + "FROM RefTbAppTransaction A, RefVwVisitType B "
            + "WHERE A.reqVisitType.id = B.id AND A.toEstCode = :toEstCode "
            + "AND A.referralDate BETWEEN TRUNC(:fromDate) AND TRUNC(:toDate) " 
            + "and (COALESCE(:fromEstCode, null) is null or A.fromEstCode in :fromEstCode)"
            )

Now it’s work fine but I want if fromDate is null to bring all data till toDate and if toDate is null to bring all data from selected date in fromDate till current date and if both are null to ignore the condition

Answer

You can try next pattern

where (:fromDate is null or A.referralDate >= :fromDate)
and (:toDate is null or A.referralDate <= :toDate)

Advertisement

Answer