CODEWITHSUNDEEP
sqljasper-reports
mikeb
mikeb

Reputation: 727

Conditions on an sql query to return specific records

I'm creating an sql query where i have 3 fields --> NUMBER,COMPANY_NAME,END_DATE. What i'm trying to do is give the user the option to whether input or not input NUMBER and COMPANY...But END_DATE must be mandatory. However when i leave both NUMBER and COMPANY empty, and only input END_DATE, i don't receive any record. Any help?

where L.NUMBER = $P{LEASE_NUMBER}
OR L.COMPANY_NAME = $P{COMPANY_NAME}
AND (L.END_DATE BETWEEN $P{DateFrom} AND $P{DateTo}
OR $X{EQUAL, L.END_DATE, dateRange})

Upvotes: 0

Views: 51

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

This may work

WHERE (L.NUMBER       = $P{LEASE_NUMBER} OR $P{LEASE_NUMBER} = '')
  AND (L.COMPANY_NAME = $P{COMPANY_NAME} OR $P{COMPANY_NAME} = '')
  AND L.END_DATE >= $P{DateFrom} AND L.END_DATE <= $P{DateTo}

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I think this is closer to the logic you are looking for:

where (L.NUMBER = $P{LEASE_NUMBER} OR $P{LEASE_NUMBER} IS NULL) and
      (L.COMPANY_NAME = $P{COMPANY_NAME} OR $P{COMPANY_NAME} IS NULL) and
      (L.END_DATE BETWEEN $P{DateFrom} AND $P{DateTo} OR
       $X{EQUAL, L.END_DATE, dateRange}
      )

I'm not sure about the expression on time. Your question has not explanation of DateFrom.

Upvotes: 2

Related Questions