A Quick Report's WHERE clause compares a column against a parameter using plain equality (`spr.MANAGER = '&MANAGER'`), and it works correctly when a value is entered, but returns 'no data found' when the parameter is left empty, even though there are genuinely matching rows expected. Using `spr.MANAGER IS NULL` instead returns results as expected, but that's not the actual intended behavior when the parameter is blank.

Q: A Quick Report's WHERE clause compares a column against a parameter using plain equality (`spr.MANAGER = '&MANAGER'`), and it works correctly when a value is entered, but returns 'no data found' when the parameter is left empty, even though there are genuinely matching rows expected. Using `spr.MANAGER IS NULL` instead returns results as expected, but that's not the actual intended behavior when the parameter is blank.

Use spr.MANAGER LIKE NVL('&MANAGER', '%') instead of a plain equality comparison. When the parameter is left empty, NVL substitutes in a wildcard '%', which LIKE matches against every value, effectively showing all rows with no filtering applied at all; when the parameter has an actual value entered, LIKE against that exact value behaves the same as a normal equality match would for typical exact-value entries.

Answer

Use spr.MANAGER LIKE NVL('&MANAGER', '%') instead of a plain equality comparison. When the parameter is left empty, NVL substitutes in a wildcard '%', which LIKE matches against every value, effectively showing all rows with no filtering applied at all; when the parameter has an actual value entered, LIKE against that exact value behaves the same as a normal equality match would for typical exact-value entries.

← Browse Reporting and analytics questions