Wednesday, May 26, 2010

Select all data when parameter is null

Sometimes we need to display data based on report parameter and sometimes when user does not input anything, the default action is to display all data.
There are three ways we can use on WHERE clause on SQL:
where column_name = nvl(:parameter, column_name)
or
where column_name like nvl(:parameter, '%')
or
where (column_name = :parameter or :parameter is null)

No comments: