Optional SQL Restrictions

Web Central supports optional restrictions that can be enabled or disabled using parameters passed from the view to the data source. This feature allows applications to define complex SQL restrictions in server-side data sources, instead of passing them from Java Script code:

  • Optional SQL clauses inside SQL queries. Reports that use complex SQL queries can include optional SQL clauses in specific parts of the query. Optional clauses apply only if the user interface form passes a parameter that enables the clause.
  • Optional SQL restrictions for field values. Complex filter consoles with multiple optional fields can include an optional restriction per field. Restriction applies only if user supplies a field value.
  • Shared optional SQL restrictions. Add-in managers can define optional restrictions in reusable data sources, then use them in other, view-specific data sources.

Examples below illustrate use of boolean parameters, and several other ways to deploy optional SQL restrictions.

Boolean parameters

Formerly, parameters with boolean values were defined using "verbatim" data type:

<dataSource> ... <parameter name="hasAvailableCapacityOnly" dataType="verbatim" value="false"/> <!-- This restriction is enabled if the client passes the hasAvailableCapacityOnly parameter = true. --> <restriction type="sql" enabled="hasAvailableCapacityOnly" sql="EXISTS (select 1 from rmcat where rmcat.rm_cat = rm.rm_cat and rmcat.occupiable = 1) AND rm.cap_em > 0"/> </filter>

Web Central checks whether values are "true" or "false".

Optional SQL clauses inside SQL queries

Optional SQL clauses inside SQL queries apply to these situations:

  • Data source contains a complex SQL query. The query includes optional SQL clauses that should, or should not, execute when user selects various options in the filter console.
  • SQL query includes optional clauses that include ${parameters} expressions.
  • Java Script code defines optional SQL clauses as string variables.
  • Java Script code passes optional SQL clauses – as data source parameters – to the server.

The objective is to remove optional SQL clauses from Java Script code, and pass only parameters to the server. These parameters enable or disable SQL clauses defined in the data source.

Apply an optional clause within a SQL query

To apply an optional clause inside a custom SQL query:

  • Add a boolean parameter that enables or disables the optional clause. Parameter is applied from Java Script code.
  • Add a restriction that specifies the optional SQL clause. Specify the restriction ID, so data source does not apply it automatically.
  • Use an expression to insert the restriction into the SQL query.

Note the following code sample, with comments:

<dataSource> <!-- This parameter enables or disables the optional restriction. --> <parameter name="hasAvailableCapacity" dataType="boolean"/> <!-- The optional restriction. The data source does not apply restriction with id attributes automatically. --> <restriction id="hasAvailableCapacityRestriction" enabled="hasAvailableCapacity"> ... </restriction> <!-- The SQL query that uses the optional restriction. --> <sql>SELECT ... FROM ... WHERE (EXISTS ... WHERE ... ${sql.getRestriction('hasAvailableCapacityRestriction')})</sql> </dataSource>

If necessary, use the same restriction in multiple parts of the SQL query.

Enable or disable restrictions

If you disable a restriction, the SQL query contains a dummy clause, 1=1, as a placeholder. A placeholder is typically required if you use the restriction inside the WHERE clause of the SQL query.

SELECT ... FROM ... WHERE (EXISTS ... WHERE ... (1=1)})

For cases where you include more than one restriction, you may need a different placeholder, or no placeholder at all. To override the default placeholder, add a second parameter to the expression:

<dataSource> <!-- These parameters enable one of the two restrictions below. --> <parameter name="useCf" dataType="boolean"/> <parameter name="useCfWorkTeam" dataType="boolean"/> <!-- The alternative restrictions. Only one should be enabled. --> <restriction id="cfRestriction" enabled="useCf">SELECT cf.work_team_id FROM cf WHERE ...</restriction> <restriction id="cfWorkTeamRestriction" enabled="useCfWorkTeam"> SELECT cf_work_team.work_team_id FROM cf_work_team WHERE ...</restriction> <!-- The SQL query that uses one of the restrictions. --> <sql>SELECT ... FROM ... WHERE wr.wr_team_id IN ${sql.getRestriction('cfRestriction', '')} ${sql.getRestriction('cfWorkTeamRestriction', '')})</sql> </dataSource>

Share optional SQL restrictions among data sources

Add-in managers can define an optional restriction in one data source, then reuse it in other data sources. To share an optional restriction among multiple data sources:

  • Define the optional restriction, and a parameter that enables or disables it, in a shared data source. If possible, place the data source in a separate .axvw file, then load the file into any view that requires it.
  • Refer to the restriction in any SQL query of the data source that needs to use the optional restriction. Specify the shared view name and data source ID:

<sql>SELECT ... FROM ... WHERE wr.wr_team_id IN ${sql.getRestriction('ab-bldgops-wr-list.axvw', 'wrDS', 'cfRestriction', '')}</sql>

  • Apply the parameter value, from Java Script code, to the panel that displays data.

Add an optional SQL restriction for a field value

To add an optional SQL restriction for a field value:

  • Add a text parameter for the field value.
  • Add code that specifies the SQL restriction. The restriction applies only if the field contains a value.

Refer to the code sample below, with comments:

<dataSource> <!-- The client passes field values entered by the user as parameters. --> <parameter name="partId" dataType="text" value=""/> <parameter name="partClass" dataType="text" value=""/> <!-- This restriction is enabled if the partId parameter is not empty. --> <restriction type="sql" enabled="partId"> <sql>pt_store_loc.pt_store_loc_id in (select pt_store_loc_id from pt_store_loc_pt where pt_store_loc_pt.part_id=${parameters['partId']})</sql> </restriction> <!-- This restriction is enabled if the partClass parameter is not empty. --> <restriction type="sql" enabled="partClass"> <sql>pt_store_loc.pt_store_loc_id in (select pt_store_loc_id from pt_store_loc_pt where pt_store_loc_pt.part_id in (select part_id from pt where pt.class=${parameters['partClass']}))</sql> </restriction> </dataSource>

Use a numeric parameter

To use a numeric parameter rather than a text parameter:

<dataSource id="roomAreas"> <table name="rm"/> <field name="rm_id"/> <field name="area_total"/> <parameter name="minArea" value="" dataType="number"/> <restriction type="sql" enabled="minArea" sql="rm.area_total > ${parameters['minArea']}"/> </dataSource>

Pass multiple values as a restriction

To pass multiple field values as a restriction:

// add single or multiple values restriction clause var restriction = new Ab.view.Restriction(); if (console.hasFieldMultipleValues('wr.bl_id')) { var valuesArray = console.getFieldMultipleValues('wr.bl_id'); restriction.addClause('wr.bl_id', valuesArray, 'IN', 'AND'); } else { var value = console.getFieldValue('wr.bl_id'); restriction.addClause('wr.bl_id', value, '=', 'AND'); }