Geek asks and answers

Zero date in search query

This is a simple question, but I can’t figure out how to do it at the moment.

I have a date field in a search query. The request is not dynamic, or it would be easier. I need to return records that correspond to the entered date or if the date is not specified, then it should return everything.

This is what I have, but it does not work. It does not return any rows when there are criteria or when there are none.

AND ( (table.dateField = p_dateField) 
   OR (table.dateField = table.dateField and table.dateField is null))

early.

After an hour of working with this, I came up with this:

and (  
      ( p_dateField IS NOT NULL AND table.dateField = p_dateField)
         OR ( p_dateField IS NULL AND (table.dateField is null or (table.dateField is not null))
     )

It works for several tests that I was able to run against it. If someone can suggest a better method, please do.

Thank!

+3
source share
4 answers

You tried:

AND ( (table.dateField = p_dateField AND NOT(p_dateField is NULL)) OR p_dateField is NULL)

I assume p_dateField is a Date parameter

+3

, :

where previous_conditions
  AND ((table.dateField = p_dateField) or (p_dateField is null))
  and other_conditions;
+3

:

AND table.dateField = NVL(pdateField, table.dateField)

, table.dateField . , :

AND NVL(table.dateField, SYSDATE+10000) = 
        NVL(pdateField, NVL(table.dateField, SYSDATE+10000)

It is assumed that SYSDATE + 10000 is a value not contained in your data.

+1
source

I would try using the NVL clause in the OR section:

AND ( (table.dateField = p_dateField) 
      OR (NVL(p_datefield, to_date('01/01/1901','MM/DD/YYYY')) =
           to_date('01/01/1901,'MM/DD/YYYY')))
0
source

More articles:


All Articles