Oracle Reports: Setting a Runtime Where Clause in the Report SQL 1

[mgmonto]

I have a report that has a start date as an in parameter that can either have a value or be null at runtime. The where clause of my report sql is:
where date_received >= _start_date

If I don't put a value in for _start_date then no data is returned. How do I set up the where clause of the query so that if _start_date is null, then the query should return all records without regarding a start_date? The where clause must also address the potential of _start_date having a valid value.

Thanks for your assistance.

 

[nagornyi]

where date_received >= decode(nvlp_start_date, 'X'), 'X', date_received, _start_date)

 

[urchin556]

The second option is to create write a code in after parameter form and then use it as lexical parameter.

For e.g

After parameter form

if _start_date is not null then
lp_date := ' and date_received >= _start_date '
end if;

and in the main query add the lexical parameter &lp_date

This approach will be suitable when there are lot of parameters in the report.