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

Question

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

talex · Accepted Answer · 2021-04-18 07:22:09Z

2

You can try next pattern

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

answered Apr 18, 2021 at 7:22

talex

20.8k3 gold badges34 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hamda Over a year ago

still it's give me ` "A Null Point Exception has occured ..."`

talex Over a year ago

It is different problem. I can't help you with that.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related