Query Filters
Setting Query Filters
Query filters are used to restrict the data rows returned by the data type view query. Query filters are added to a data type view by clicking on the Query filter icon in the data type view tool bar. This will open up the Query Filter window for the current data type view. For those familiar with the SQL database query language, the Query Filter window is used to construct the SQL WHERE clause that will be used when executing the query to populate the data type view with data rows.
The Enable query filter checkbox at the top left corner of the Query Filter window can be used to toggle the filter on or off without having to remove it.
Query Filter Attributes
A table of all the attributes available for display appears across the top third of the Query Filter window. Selecting the Show all attributes option will add all of the attributes that exist in the model view to the table, including attributes that are not marked as available in the model view.
The attributes shown in the table are available for use in constructing the filter. The attribute table shows the attribute's display name and data type. The Relationship column in the attribute table shows the data model data type that the attribute actually belongs to. This is needed because the data type view may represent a join between multiple related data types in the data model that you are viewing. Click the information icon to open a window that shows a description of the attribute's purpose.
Click on the button next to any attribute to add it to the filter expression table that appears in the middle section of the Query Filter window.
Filter Expressions
Query Filter Expressions
Query filter expressions are tests that are applied to each row of data to decide it the row of data should be displayed in the Data Selector or if it should be omitted. The filter expression table displayed in the center section of the Query Filter window is actually a representation of the SQL WHERE clause that will be added to the query executed by the Data Selector against the OpenSpirit data connectors to populate the data type view in the Data Selector.
An optional comment field is provided so you can document the purpose of each filter expression.
Clicking on the View Query button will display a window that shows the SQL that the data selector will execute to populate the data type view in the Data Selector.
Attributes can be removed from the filter expression table by clicking on the remove icon . The up
and down
arrows are used to reorder query expressions in the table.
Query Filter Operators
The relational operator to be used in the filter expression should be selected when adding an attribute to the filter expression. The operators that are available for selection depend on the attribute's data type.
The following table shows the operators that can be used for each attribute data type.
Operator | Description | Supported Data Types | ||||||
---|---|---|---|---|---|---|---|---|
= |
The attribute must be exactly equal to a specified value. Use of the equals operator with FLOAT, FLOAT_QUANTITY, DOUBLE, and DOUBLE_QUANTITY attributes is strongly discouraged. Floating point values are subject to rounding errors during unit conversion. Also, the values presented in applications, including the Data Selector, are typically not showing the full precision of the values. Consider using a less than expression combined with a greater than expression to create a tolerance range when filtering on floating point values. |
BIGINT |
||||||
= Attribute |
The attribute must be exactly equal to the value of another attribute of the data type. The other attribute must have the same data type in order for it to appear in the value selection list. |
BIGINT |
||||||
!= |
The attribute must be different from a specified value. Use of the not equals operator with FLOAT, FLOAT_QUANTITY, DOUBLE, and DOUBLE_QUANTITY attributes is strongly discouraged. Floating point values are subject to rounding errors during unit conversion. Also, the values presented in applications, including the Data Selector, are typically not showing the full precision of the values. Consider using a less than expression combined with a greater than expression to create a tolerance range when filtering on floating point values. |
BIGINT |
||||||
< |
The attribute must be less than a specified value. A lexical comparison is used when comparing VARCHAR attributes. |
BIGINT |
||||||
> |
The attribute must be greater than a specified value. A lexical comparison is used when comparing VARCHAR attributes. |
BIGINT |
||||||
IN |
The attribute must have an exact match with one value in a list of comma separated values Note: You can paste multiple lines of text from the system clipboard into the value field and the multiple lines will be converted to a comma separated list of values.
|
BIGINT |
||||||
NOT IN |
The attribute must not have an exact match with any value in a list of specified values. Note: You can paste multiple lines of text from the system clipboard into the value field and the multiple lines will be converted to a comma separated list of values.
|
BIGINT |
||||||
LIKE |
The attribute must partially match a specified value. Partial match is specified using special wildcard characters.
For example:
|
VARCHAR |
||||||
NOT LIKE |
The attribute must not partially match a specified value. Partial match is specified using the special wildcard characters described above in the description of the LIKE operator. |
VARCHAR |
||||||
IS NULL |
The attribute cannot have any value in the database. |
<all types> |
||||||
IS NOT NULL |
The attribute can have any value except the null value. |
<all types> |
||||||
SIMILAR TO |
The attribute must partially match a specified value. Partial match is specified using regular expression syntax. |
VARCHAR |
||||||
NOT SIMILAR TO |
The attribute must not partially match a specified value. Partial match is specified using regular expression syntax. |
VARCHAR |
||||||
WITHIN |
The attribute must be spatially within the spatial scope that has been set on the OpenSpirit Desktop. Spatially within means the geometry of the attribute lies in the interior of the Data Selector's spatial scope. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
EQUALS |
The attribute must be "spatially equal" to the spatial scope that has been set on the OpenSpirit Desktop. Spatially equal means the geometries are topologically equal. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
DISJOINT |
The attribute must be "spatially disjoint" from the spatial scope that has been set on the OpenSpirit Desktop. Spatially disjoint means the geometries have no point in common. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
TOUCHES |
The attribute must "spatiallytouch" the spatial scope that has been set on the OpenSpirit Desktop. Spatially touch means the geometries have at least one boundary point in common, but no interior points. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
OVERLAPS |
The attribute must "spatiallyoverlap" the spatial scope that has been set on the OpenSpirit Desktop. Spatially overlap means the geometries share some but not all points in common, and the intersection has the same dimension as the geometries themselves. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
CROSSES |
The attribute must "spatially cross" the spatial scope that has been set on the OpenSpirit Desktop. Spatially cross means the geometries share some but not all interior points,and the dimension of the intersection is less than that of at least one of the geometries. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
INTERSECTS |
The attribute must "spatially intersect" the spatial scope that has been set on the OpenSpirit Desktop. Spatially intersect means the geometries have at least one point in common. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
||||||
CONTAINS |
The attribute must "spatially contain" the spatial scope that has been set on the OpenSpirit Desktop. Spatially contains means the geometry of the Data Selector's spatial scope lies in the interior of the attribute. This operator conforms to the spatial relationship predicates defined in the OpenGIS Simple Feature Access specification. |
GEOMETRY |
Query Filter Values
Most filter expression operators also require a value to be entered or selected. The exceptions are the IS NULL, IS NOT NULL, and the spatial operators WITHIN, EQUALS, DISJOINT, TOUCHES, OVERLAPS, CROSSES, INTERSECTS, and CONTAINS. These operators do not require an expression value to be entered in the filter.
Values for filter expressions using the = Attribute operator are entered by selecting one of the attributes in the dropdown. The dropdown contains all of the attributes that have the same data type as the expression attribute.
Values for other operator types are entered in different ways depending on the attribute data type and the expression operator that is selected.
VARCHAR attributes
Values for filter expressions on VARCHAR attributes can be entered by typing in the Value field, or by clicking on the adjacent Lookup button which will query the Data Selector's current data source(s) and display a distinct set of values that currently exist in the data source(s). A value can then be selected from the lookup list. The lookup selection list for the IN and NOT IN operators for VARCHAR attributes allows for multiple values to be selected. The values should be separated by commas when entered manually.
The list displayed by clicking on the Lookup button will be either a complete list of allowable values, or it will be a list of the distinct set of values that currently exist in the selected data source(s). The lookup list for attributes that are constrained to a list of allowable values will appear as a scrolled list of all the possible values for the attribute. The title bar above the list will contain the attribute name followed by Allowable Values.
The allowable values list provides no indication which values are currently in use in the data source(s). All possible values are shown.
The lookup list for attributes that are not constrained to a set of allowable values appears as a scrolled list that contains the distinct list of values that are currently in use in the data source(s). The title bar above the list will contain the attribute name followed by Unique Values. A Row Limit field and a Get Values button appear on distinct values lists. They do not appear on allowable values lists.
The Row Limit field below the list of values provides protection against attempting to display a very large number of values. A larger or smaller number of values can be shown in the list by entering a different row limit value and clicking on the Get Values button to re- query the data source(s).
STRING_ARRAY attributes
Values for filter expressions on STRING_ARRAY attributes can be entered when using the IN or the NOT IN operator. Values are entered by typing in the Value field. A single value can be entered or a comma separated list of values can be entered.
INTEGER, BIGINT, TINYINT, and SMALLINT attributes
Values for filter expressions on integer attributes are entered by typing an integer number in the Value field. The value must be a positive or negative integer.
FLOAT and DOUBLE attributes
Values for filter expressions on real number attributes are entered by typing a number in the Value field. The value must be a positive or negative number expressed in decimal number format with a period character separating the whole number part from the fractional number part.
FLOAT_QUANTITY and DOUBLE_QUANTITY attributes
Values for filter expressions on FLOAT_QUANTITY and DOUBLE_QUANTITY attributes are entered by typing a number in the Value field. A unit of measure selection is also required in order for the numeric value to be fully defined. Unit conversions will be performed if needed when applying the filter during query execution. The unit selected in the filter expression will not affect how the values returned by the query are displayed in the Data Selector. The unit is only used to insure proper unit handling when applying the filter.
TIMESTAMP attributes
Values for filter expressions on TIMESTAMP attributes must be entered using a specialized date/time selection window. Pressing the Select button next to the filter expression displays the date/time selection window.
Clicking on the month, year title will zoom the calendar out for selecting any month in the year.
Clicking on the year title will zoom the calendar out even more for selecting any year within the decade. Clicking on the decade will zoom the calendar out to select a century. Century selection is the zoom limit.
BOOLEAN attributes
The value used for BOOLEAN attribute is selected from a dropdown that contains TRUE and FALSE.
GEOMETRY attributes
The value used for GEOMETRY attributes is determined by the data selector's spatial scope. The value is entered using the Data Selector's spatial scope selector. The spatial scope selector is opened by clicking on the Select Spatial Scope icon in the Data Selector tool bar. See the Setting a Spatial Scope section of this guide for information about setting the data selector's spatial scope.
UNIT attributes
Values for filter expressions on UNIT attributes must be entered using a specialized unit selection window. Pressing the Lookup button next to the filter expression displays the unit selection window.
Select a unit measurement in the top section of the window to display the units that belong to a unit measurement category. Select the desired unit in the lower section of the window.
DATAKEY attributes
Values for filter expressions on DATAKEY attributes must contain a valid OpenSpirit data key string. Data keys are represented using XML. Data key strings can be pasted into the Value field using the system clipboard's copy/paste feature. Data key strings can be obtained from a variety of sources. They can be obtained by dragging and dropping rows from the data selector to a Microsoft Word or Excel document. They can sometimes be obtained from Copy Manager logs or from OpenSpirit data connector log files.
Query Filter Predicate
The query filter expression Predicate selector appears when adding more than one attribute to the query filter expression table. The predicate indicates how the filter expression should be logically combined with the expression immediately below it. The predicate choices are AND and OR.
The AND predicate combines the expressions by requiring both to evaluate to true in order for a data row to be returned by the query. Combining two expressions using the OR predicate will return a data row if either expression evaluates to true. Expressions are most commonly combined using the AND predicate.
It is highly recommended to use the grouping feature when mixing use of AND and OR predicates in a query filter. The grouping feature is used to impose an evaluation order on the combined expressions. Expression grouping can be enabled by selecting the Use Grouping option above the filter expression table. Selecting the grouping option causes two additional columns to appear in the filter expression table. The columns contain drop down selectors that are used to add parentheses around the expressions. Up to two levels of parentheses nesting can be selected. The opening and closing parentheses count must match.
A Validate Query button is provided to test the filter expressions to insure parentheses matching. The validation also insures that expression values have been entered where required. Validation will run automatically when closing the Query Filter window using the Ok button.
Query Filter View Scope & Spatial Filtering
The bottom section of the Query Filter window contains options for selecting the View Scope of the data type view and for controlling spatial filtering. The View Scope selection option is the same as on the data type view tool bar. The Enable spatial scope option turns spatial filtering on or off. A spatial scope must be set on the desktop in addition to enabling it in the Query Filter window for spatial filtering to be performed. The Spatial Attribute selection determines the attribute that the spatial filtering will be applied to. All spatial attributes available for display in the data type view will appear in this dropdown selector.