| Warning | ||
|---|---|---|
| ||
This feature is deprecated. Please use Query Control instead. |
Performs relational database queries on domain SQL database.
Design
Auto Search
Specifies the query control should perform a search immediately when visible. Visibility of control can be controlled by Rules or Section Name properties. When Filter content is not visible when this attribute is set to True value Filter content is not visible.
Auto Select All (v4.5)
Specifies the query result picker is must be shown. Result The result picker allows to select selecting individual rows to edit. If you work with too many rows, a picker is a better method for performance. When set to True, all matching rows are immediately imported.
Clear First
Specifies the before query results populated on data model all existing records are removed.
Rules
Section Name
Name of section is for controlling read only or visible state of a section. After setting value, you can configure section in task properties.Include Page _Section Name _Section Name
Validation Group
Name of validation group rules before executing the query. When any rule is failed, an error is shown on the screen and the query is not executed.
Dynamic Rules
Through this button you can add two types of rules to the query content control. Click one of them and you will see it is listed above the button. It has two buttons to edit and delete the rule.
...
Data
Bound XPath
Changes XPath of the query content.
| Include Page | ||||
|---|---|---|---|---|
|
Data
Bound XPathInclude Page _Bound XPath _Bound XPath
XPath
XPath of data model to bind control. If not set, uses current node on the data model.
Target XPath
Specifies the xpath of target container node to import query results. Specified xpath is relative to the parent of the current node not XPath attribute.
...
Relational database queries uses use special markup to specify the query to execute. Query structure is similar to SQL Select command, but you may use sub queries subqueries and join clauses automatically handled.
...
Name="MyColumn"
Name of column or relation path. Please see Column Name for more information. Required.
Formula="None">
Aggregation formula like count, max etc. If not specified no aggregation is applied. Please see Formula for more information.
<Properties>
Column specific properties. Currently
...
Name="MyColumn"
Column name or relation path to criteria to apply. Please see Column Name for more information. Required.
Comparison="Equals"
Comparison method of criteria like equals or greater etc. If not specified Equals value is used. Please see Comparison for more information.
Condition="And"
Condition of criteria to merge with previous criteria in list. "And", "Or" values are valid. If not used "And" value is used.
Formula="None">
Aggregation formula like count, max etc. If not specified no aggregation is applied. Please see Formula for more information.
<Value></Value>
Value of criteria. Value can be specified in static value or template format like "{{Code}}" or "000-{{Code}}-1111" to dynamically evaluate.
If not specified value used as empty string.
You can use "NULL" string value to specify null value.
...
Name="MyColumn"
Column name or relation path to apply order. Please see Column Name for more information. Required.
...
Formula specifies the method of evaluation of row values. Formula enumeration can be one of following values;
| Value | Description |
|---|---|
| None | No aggregation is applied. All values in rows. |
| Count | Count of row values. |
| CountDistinct | Count of distinct values in rows. |
| Sum | Sum of row values. Valid for numeric type columns. |
| SumDistinct | Sum of distinct values in rows. Valid for numeric type columns. |
| Avg | Average of row values. Valid for numeric type columns. |
| AvgDistinct | Average of distinct values in rows. Valid for numeric type columns. |
| Min | Minimum value in rows. |
| Max | Maximum value in row values. |
Comparison
Specifies the comparison method of criteria value.
| Value | Description |
|---|---|
| Equals | All rows with same value. |
| LessThan | All rows that less than criteria value. |
| LessThanOrEqualTo | All rows that less or equals to criteria value. |
| GreaterThan | All rows that greater than criteria value. |
| GreaterThanOrEqualTo | All rows that greater or equals to criteria value. |
| Different | All rows with different than criteria value. |
| Like | All rows that likes to criteria value. Criteria value can be used with * (star character) Like *ABC, ABC*, *ABC* |
Column Name
Column names can be specified as directly column name or relation path. Relation paths can be specified as "Relation.Relation.Column" format.
For example, you can use the following values as column names for above relational database model.
| Column Name | Description |
|---|---|
| Name | Name of customer |
| Orders.Id | Id of order |
| Orders.Product.Name | Name of ordered product |
Example Query
Following The following query returns the all customer name names and count counts of orders by product name criteria. Each row also contains the list of related customer orders as a child rows.
| Code Block |
|---|
<Query>
<Parameters>
<TargetSchema>Customer</TargetSchema>
<TargetTable>MySchema</TargetTable>
</Parameters>
<Columns>
<Column Name="Name" Formula="None" />
<Column Name="Orders.Id" Formula="Count" />
</Columns>
<Where>
<Criteria>
<Criteria Name="Orders.Product.Name" Comparison="Like">
<Value>{{ProductName}}</Value>
<IgnoredValues>
<Value></Value>
<Value>*All*</Value>
</IgnoredValues>
</Criteria>
</Criteria>
</Where>
<Order>
<Order Name="Orders.Product.Name" Type="Ascending"/>
</Order>
<SubQueries>
<Query Name="Orders">
<Columns>
<Column Name="Id" />
<Column Name="ProductId" />
</Columns>
<Order>
<Order Name="Product.Name" Type="Ascending"/>
</Order>
</Query>
</SubQueries>
</Query> |
...