Dynamic Where Clauses

Datasource Definition Criteria feature enables a developer to store individual where clause predicates in the Formspider application repository and apply them to a datasource as she sees fit. This feature is especially useful for datasources used to display results for a search an application user performs.

Prior to Formspider 1.9, the developer had to write every predicate as part of the SQL query of the datasource definition using bind variables. Moreover, she had to account for the possibility that a bind variable might be null while executing the query.

select a.employee_id, a.first_name, a.last_name
from   employees a
where (a.employee_id=:empid or :empid is null)
and (a.first_name like '%'||:firstname||'%' or :firstname is null)
and (a.last_name like '%'||:lastname||'%' or :lastname is null)

This method has several disadvantages but most importantly it might not produce the optimal execution plan for the intended query. The developer can help Oracle to produce a better execution plan by removing the irrelevant predicates from the SQL statement. In most cases this means to remove the predicates that include a bind variable with null value from the where clause. To achieve this, a developer can create each predicate as a criterion in the datasource definition instead of adding it to the datasource definition SQL statement. Formspider will use the relevant criteria and create the optimal SQL statement to execute.

Before working with the new criteria screens in the datasource definition dialog the developer must create the bind variables she wants to use in the datasource definition, using the bind variables tab.

The Criteria Tab

The Criteria screen is where the developer manages the predicates for the datasource definition. Each criterion is a predicate.

The lower section lists all the criteria defined for the datasource definition. It also has buttons to create, edit and delete criteria.

There are two types of criteria:

Simple Criterion

A simple criterion is a where clause predicate. It has the following attributes:

name: Unique identifier of the criterion. In a datasource definition, two criteria cannot have the same name.

column: The datasource definition column this criterion applies to. (The left side of the operator.)

defaultOperator: The operator being used in the criterion. The available operators (=, <, >, =<, >=, like, is null, is not null, !=, not like) depend on the data type of the column.

statement: the SQL fragment that returns a value to be used on the right side of the operator. A simple criterion’s statement can only have one bind variable in it. Example: ‘%’||upper(:bindvar1)||’%’

ignoreNullValues: Boolean value which indicates that the criterion must be ignored if the referenced bind variable’s value is null.

required: indicates if this criterion must be in the criteria for the criteria to be applied to the datasource.

Advanced Criterion

An advanced criterion has a less structured form. It may contain more than one bind variable. It has the following attributes:

name: Unique identifier of the criterion. In a datasource definition, two criteria cannot have the same name.

statement: an SQL where clause predicate that returns true or false. Example: create_date between :bindvar1 and :bindvar2

ignoreNullValues: Boolean which indicates that the criterion must be ignored if all the bind variables it contains have the value null.

required: indicates if this criterion must be in the criteria for the criteria to be applied to the datasource. The top section displays attributes and features for the entire group of criteria.

Criteria Attributes

Override Default Structure: If checked, the developer can override the default structure of the datasource definition where clause.

Structure: Defines how the set of criteria should be pieced together to create a valid SQL where clause. By default, the predicates are put together using the AND logical operator.

To override the default structure of a where clause, check the “Override Default Structure” and modify the text in the structure text area.

Syntax to Define a Structure

For example, assuming the datasource definition has three criteria with names criterion1, criterion2 and criterion3, the following syntax can be used to define a new structure:


#{cr.criterion1} AND (#{cr.criterion2} OR #{cr.criterion3})

Enforce At Least One Criterion: If checked it enforces at least one criterion to exist before applying the criteria to the datasource. This feature is used to prevent searches where the user provides no search criteria. Validate Structure button helps the developer to verify the syntax of a customized where clause structure. In the default where clause structure, each criterion is pieced together using the AND logical operator.

API’s

Formspider has the following new API’s, types and constants in the api_datasource package to support dynamic where clauses:

Types:

tt_criterionName: Table type that represents a list of criterion names. An array of varchar2(255)’s indexed by binary_integer. tt_bindvarValue: Table type that represents a list of bind variable-value pairs with potential operand overrides. Type has the following columns: bindvarname_tx, value_tx, value_nr, value_dt, formatmask_tx, allowoverride_yn

Constant: (Implemented as a Function)

function all_criteria return tt_criterionName: Constant that represents a list of all criteria in the Datasource Definition. (To be used in setWhereClause procedure as the input value for the in_criterionNames_t input parameter)

Procedures:

setWhereClause(in_datasourceName_tx varchar2, in_criterionNames_t tt_criterionName, in_bindvarValues_t tt_bindvarValue, in_structure_cl clob:=keep_current_value): Updates the whereclause of a datasource using the Criteria defined in the Datasource Definition.

Parameters:

in_datasourceName_tx varchar2 : The name of the datasource to be updated

in_criterionNames_t tt_criterionName : The names of the criteria to be used. Use api_datasource.ALL_CRITERIA to use every criterion.

in_bindvarValues_t tt_bindvarValue: Bindvar name/value pairs to be set on the datasource.

in_structure_cl clob: Indicates how the where clause fragments for criteria should be pieced together to form a valid where clause

Exceptions:

e_conversion: Raised when the value supplied for a bind variable cannot be cast to its data type.

e_missingMandatoryCriterion: Raised when the list of criteria passed to the procedure is missing a criterion that is set as required.

e_atLeastOneCriterion: Raised when the setWhereClause produces a where clause that contains no criteria even though it must.

e_invalidDatasourceName: Raised when the datasource name is not found.

setWhereClause(in_datasourceName_tx varchar2, in_criterionNames_t tt_criterionName, in_bindvarValues_t tt_bindvarValue,in_structure_cl clob:=keep_current_value): Updates the whereclause of a datasource using the Criteria defined in the Datasource Definition.

Parameters:

in_datasourceName_tx: The name of the datasource to be updated

in_criterionNames_t: The names of the criteria to be used. Use api_datasource.ALL_CRITERIA to use every criterion.

in_structure_cl: Indicates how the criteria should be pieced together to form a valid where clause

Exceptions:

e_conversion: Raised when the value supplied for a bind variable cannot be casted to its data type.

e_missingMandatoryCriterion: Raised when the list of criteria passed to the procedure is missing a criterion that is set as required.

e_atLeastOneCriterion: Raised when the setWhereClause produces a where clause that contains no criteria.

e_invalidDatasourceName: Raised when the datasource name is not found.

setWhereClause(in_datasourceName_tx varchar2, in_whereClause_cl clob): Updates the where clause of a datasource.

Parameters:

in_datasourceName_tx: The name of the datasource to be updated

in_whereclause_tx: The where clause for the datasource.

Exceptions:

e_invalidDatasourceName: Raised when the datasource name is not found.

e_notSupportedAPIForReportMode: Raised when the API is called for a datasource that is in Reporting Mode

Important Note: The where clause changed by these API’s is not the where clause shown in the Query Tab of the Datasource Definition Dialog in the Formspider IDE. This is a new where clause added on top of the core Datasource Definition SQL. In other words, if the developer adds a where clause to the datasource, the SQL of the datasource changes in the following manner:

select *
from (Datasource_Definition_SQL)
where (Where_Clause)

In most cases where the criteria feature is used, the where clause of the datasource definition should be empty even though this is not a prerequisite.

Functions:

getWhereClause(in_datasourceName_tx varchar2) return clob: Returns the current parsed where clause for the data source.

Parameter:

in_datasourceName_tx: The name of the datasource

Exception:

e_invalidDatasourceName: Raised when the datasource name is not found.