Implementing Optional Filters with n=n Anchor

The recommended method for handling optional parameters in `WHERE` clauses, specifically to avoid potential performance problems associated with coding like `nvl(:bind_variable, column_name)`, is to use the `n=n` anchor. Your main report SQL should include the clause `where 1 = 1` which serves as the anchor point. The parameter setup then defines the SQL text for the restriction (e.g., `upper(hp.party_name) like upper(:customer_name)`) and specifies the `n=n` anchor.

Blitz Report intelligently manages this process: it only inserts the parameterized `WHERE` clause into the main extraction SQL at runtime if the user actually enters a value for that specific parameter. If the user provides a value, the corresponding `WHERE` clause is injected at the position of the `1=1` anchor. If no value is provided, the original `where 1=1` clause remains, and no additional filtering is applied, ensuring high performance.

The primary limitation of the `n=n` anchor is that it can only be used for injecting additional `WHERE` clauses, because the syntax `1=1` must remain in the run-time SQL. In contrast, the `&lexical` anchor offers much greater flexibility, capable of adding any required SQL snippet, such as dynamic tables, `ORDER BY` clauses, or even full subselects, making it suitable for structural changes beyond just filtering.

For multi-value selection to work automatically using the standard parameter functionality, the parameter SQL text must have the table column on the left side and the `:bind` variable on the right side of the comparison operator (e.g., `column_name = :bind_variable`). If the SQL text is too complex, or if you use a non-standard operator like `>=:bind_variable`, automated IN-clause replacement is not possible, and the multiple values checkbox will be unavailable for that parameter. However, for complex multi-value scenarios where the query itself depends on the input, you can leverage the `xxen_util.contains` function in your logic.

While Blitz Report supports standard bind parameters similar to other reporting solutions, using anchors for dynamic SQL is generally recommended to avoid performance issues associated with coding conditional logic like `nvl(:bind_variable, column_name)` for optional inputs. If you do use simple bind variables, be aware that certain words are reserved by Oracle and cannot be used as bind variable names; you can find a list of these reserved words by querying `v$reserved_words` where `reserved = ‘Y’` or `res_semi = ‘Y’`.