Skip to content

Query Content

Performs relational database queries on domain SQL database.

Design

Auto Search
Specifies the query control should perform search immediately when visible. Visibility of control can be controlled by Rules or Section Name properties. When this attribute set to True value Filter content is not visible.

Auto Select All (v4.5)
Specifies the query result picker is must be shown. Result picker allows to select individual rows to edit. If you work with too many rows picker is 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. If set to a value this section can be configured in task properties.

Validation Group
Name of validation group rules before executing query. When any rule is failed, error is shown on screen and query is not executed.

Dynamic Rules
Dynamic rules section allows to specify format and warning messages of any element and section. Dynamic rules section consists of two parts, one of them is for formatting rules and the other one is for validation rules.

  • Formatting rules, allow you to change the formats (text color, background color, style) and visibility of a control depending upon a condition. Further information here.

  • Validation rules, allow you to validate the data that is entered by the end-user. Further information here.

Data

Bound XPath
XPath of the element. If set to empty, it uses parent xpath.

XPath
XPath of data model to bind control. If not set uses current node on data model.

Target XPath
Specifies the xpath of target container node to import query results. Specified xpath is relative to parent of current node not XPath attribute.

For example, use "Filter" node for XPath attribute and use "Results" xpath for TargetXPath attribute.

<Data>
    <Filter>
        <SearchField />
    </Filter>
    <Results>
        <Row>
            <Column />
        </Row>
    </Results>
</Data>

Query Syntax

Relational database queries uses special markup to specify query to execute. Query structure is similar to SQL Select command but you may use sub queries and join clauses automatically handled.

Query Structure

<Query

Start=""
Start index of results to fetch. If not specified 0 value is used.

MaxLength="-1">
Maximum number of results to fetch. If not specified -1 (maximum results) is used.

<Parameters>
Query parameters.

<TargetSchema>MySchema</TargetSchema>
Name of schema of query target. Required.

<TargetTable>MyTable</TargetTable>
Name of table of query target. Required.

</Parameters>

<Columns>
List of columns to include in results.

<Column
The column definition. Any number of column can be specified.

Name="MyColumn"
Name of column or relation path. Required.

Formula="None">
Aggregation formula like count, max etc. If not specified no aggregation is applied.

<Properties>
Column specific properties. Currently

<XPath></XPath>
XPath of column value mapping for rows. If not specified column name is used.

<Language></Language>
By default all columns are automatically localized by user language but with this property you can ask for explicitly specify the language. If language is not available, original column value is used.

</Properties>

</Column>

<Column></Column>

<Column></Column>

</Columns>

<Where>
Query criteria and block list. Criteria can be consist of criteria definitions or block definitions to group criteria for logical conditions (and / or).

<Criteria>
List of criteria values.

<Criteria
The criteria definition. Any number of criteria can be specified.

Name="MyColumn"
Column name or relation path to criteria to apply. Required.

Comparison="Equals"
Comparison method of criteria like equals or greater etc. If not specified Equals value is used.

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.

<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.

<Properties>
Criteria specific custom properties. Currently IgnoreIfEquals only used.

</Properties>

<IgnoredValues>
The list of values to ignore criteria completely. For example; when product name criteria is set to empty value you may want to list all products, not empty named products.

<Value></Value>
Value of criteria.

</IgnoredValues>

</Criteria>

<Criteria> ............ </Criteria>

</Criteria>

<Blocks>
List of blocks to group criteria.

<Block
The block definition.

Condition="And">
Condition to merge with previous blocks. If not specified And value is used.

<Criteria>
List of criteria. Same with above criteria definition.

<Criteria> ............ </Criteria>

</Criteria>

</Block>

</Blocks>

</Where>

<Order>
List of ordering columns.

<Order
The order definition.

Name="MyColumn"
Column name or relation path to apply order. Required.

Type="Ascending"/>
Type of ordering. "Ascending", "Descending" values are valid. If not specified "Ascending" value is used.

</Order>

<SubQueries>
List of subqueries to return as related child rows.

<Query
Subquery definition. Same with above only Name attribute is required.

Name="">
Name of table relation to get rows as child. If relation is not found in table definition error is thrown.

</Query>

</SubQueries>

</Query>



Formula

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
Different All rows with different than criteria value.
Equals All rows with same value.
GreaterThan All rows that greater than criteria value.
GreaterThanOrEqualTo All rows that greater or equals to criteria value.
LessThan All rows that less than criteria value.
LessThanOrEqualTo All rows that less or equals to 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.

Home page

For example you can use 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 query returns the all customer name and count of orders by product name criteria. Each row also contains the list of related customer orders as a child rows. 

<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>