0

In BI-PUBLISHER, I have two parameters of STARTDATE and ENDDATE.

There is a field in the table ACTIVE_DATE which would be used in the above parameters.

I am using the following condition in the query:

trunc(active_date) between NVL(:P_Active_Date, trunc(active_date))
and NVL(:P_Close_Date, trunc(active_date)

If both the parameters are null, it should select all ranges of the dates.

But when I apply these parameters, it doesn't show me the correct result. Even when I select both the parameters, it still doesn't show me the correct result.

How should it be done?

Charlie
  • 8,530
  • 2
  • 55
  • 53
Imran Hemani
  • 599
  • 3
  • 12
  • 27

2 Answers2

0

In case your dates attributes have taken different hour for the same day,you can do this :

where
trunc(active_date)
between to_date( ( NVL(:p_Active_Date, trunc(active_date)) ),'DD/MM/YYYY')
and  to_date( (NVL(:p_Close_Date, trunc(active_date) )  ),'DD/MM/YYYY')

I had one problem like that and the query did not show me the records in the range of the parameters which i had input.

Hope this helps.

Anastasis
  • 192
  • 1
  • 2
  • 12
0

Try

WHERE
(:P_START_DATE IS NULL OR trunc(ACTIVE_DATE) > :P_START_DATE )
AND
(:P_END_DATE IS NULL OR trunc(ACTIVE_DATE) < :P_END_DATE )

Usually the Parameter is a date, and the table holds the start/end date though. 

WHERE
(:P_ACTIVE_DATE IS NULL  
OR ((START_DATE IS NULL OR START_DATE <= :P_ACTIVE_DATE )
AND (END_DATE IS NULL OR END_DATE > :P_ACTIVE_DATE )))

Neither of those may suite your needs, but you get how the AND/OR work with the IS NULL.

EdHayes3
  • 1,777
  • 2
  • 16
  • 31