View Article

Introduction

The Query Builder allows you to add a query to the entity without having to manually write the SQL. Use the Query Builder interface to drag and drop the query elements you want and Xelence will generate the query for you. Then you can call the query elsewhere in your project.

Prerequisites

An entity must already be created to begin this process.

Detailed Steps

Step 1. Search for and open the entity, then open the Queries section.

This example uses the Customer entity. The Queries section is where the entity manages its queries.

Step 2. Optionally, add a new group, name the group, then click OK.

Click the + icon to add a new group.

Give the group a descriptive name so it's always easy to find, and make sure you don't duplicate a group name that already exists.

Every query must belong to a group. If no appropriate group for your query exists, you must create one.

Step 3. Add a new query to the group, select the Query Type, then click OK.

Click the + icon to add a new query.

The Query Type determines how you will build the query in Xelence. This example uses Query Builder.

Query means you will simply type the SQL code.
Query Builder means you will drag and drop query elements and Xelence will generate the SQL code for you.

Step 4. Enter the details, then click OK.

Give the query a descriptive name so it's always easy to find, and make sure you don't duplicate a query name that already exists.

In Selected Entities, add the entities whose fields you want to use to build the query. This example adds entOrder and entOrderDetails to the Selected Entities section.

Step 5. Add the Output Columns.

The Output Columns panel displays the columns that will be included in the query's results. To add columns, drag and drop the desired column from the Data Source tab.

Step 6. Optionally, create a custom expression, then enter the properties.

A custom expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. To add an expression, click the Add Custom Expression icon.

The important properties are:

• ID: Programmatic ID of the custom expression. This property is required.
• Data Type: Specifies the data type of the value the custom expression returns.
• Functions Category: Specifies various types of functions. Select Common Functions to further select the Function Sub-Category.
• Functions Sub-Category: Specifies various sub types of functions.

Functions Sub-Category options are:

Text: Performs operations or on string values. For example: SUBSTRING, REPLACE, CHARINDEX, etc.
Date and Time: Performs operations on date time values. For example: GETDATE, DATEDIFF, DATEADD, etc.
Mathematical: Performs operations on numeric values. For example: CEILING, FLOOR, ABS, SQRT, ROUND, etc.
Aggregate: Groups the values of multiple rows based on certain input criteria to form a single meaningful value using arithmetic operators. For example: AVG, COUNT, COUNT_BIG, SUM, MIN, MAX, etc.

Step 7. Add the function to the expression, update the function, then click OK.

Click the Add Function icon, then update the function so it uses the desired query elements.

Click OK to close the window and make the expression available to the query.

Step 8. Add the expression to the query.

Step 9. Configure the Output Columns.

When you have added the desired columns, enter the properties. The important properties are:

• Top Records: Specifies the number of records to return from the database and is useful for large tables with thousands of records.
• Distinct Records: A column often contains many duplicate values, and you may only want to list the distinct or unique (different) values. If checked, this returns only distinct output values.
• Name: Column name.
• Alias: Temporary name for the column for the purposes of the query.
• Aggregate: Groups the values of multiple rows based on certain input criteria to form a single meaningful value (automatically populates the Group Columns section).

Aggregate options are:

Count: Returns the number of records that match the criteria.
Max: Returns the largest value of a specified column.
Min: Returns the smallest value of a specified column.
Sum: Returns the sum of a specified column.
Avg: Returns the average of a specified column.

Step 10. Optionally, configure the Sort Columns section.

The Sort Columns section sorts the columns in ascending or descending order. By default, the added columns are sorted in ascending order.

The important properties are:

• Name: Specifies the sort column name. Drag and drop the desired column.
• Sort Type: ASC (ascending) or DESC (descending). You can change the order of the columns by using the arrows icon.

Step 11. Optionally, configure the Group Columns section.

Group Columns allows you to group the result based on one or more columns. By default, Xelence populates this section with the Output Columns that are not part of aggregate functions.

Step 12. Optionally, add a condition, then configure the condition.

A condition filters the records. Click the Add Condition button to add a condition.

Build the condition with the following:

• Attribute: Allows you to enter the entity attribute.
• Parameter: Allows you to enter the query parameter.
• Constant: Allows you to enter a constant value.
• Expression: Allows you to enter another expression.

You can also click the Add Group button to add a group of two or more conditions.

If you use multiple conditions, you can combine them with AND or ANY, plus the operators: In, NotIn, Contains, Between, IsNull, IsNotNull, IsEqualTo, IsNotEqualTo, IsLessThan, IsLessThanOrEqualTo, IsGreaterThan, IsGreaterThanOrEqualTo, StartsWith, and EndsWith.

Step 13. Optionally, configure the References section.

The References section combines or joins rows from two or more tables, based on related or similar columns between them.

The important properties are:

• Left Column: Specifies the left column in the join. Drag and drop the desired column.
• Right Column: Specifies the right column in the join. Drag and drop the desired column.
• Join: Joins the left column and right column. Select the desired option for the join.

Join options are:

Inner Join: Returns records that have matching values in both the columns.
Left Outer: Returns all the records from the left column, and the matched records from the right column.
Right Outer: Returns all the records from the right column, and the matched records from the left column.

Step 14. Save the entity.

Save the entity once you are done with entering all the objects in the query builder.

Step 15. View the SQL code.

Click the View Query icon to view the generated query. This is the line of SQL code that executes operations like retrieving data.

Step 16. Optionally, execute the query.

Click the Execute Query icon to execute the query if you want to test it. 

Step 17. Optionally, export the query.

Click the Export icon if you want to export the query to regular query format. You can enter the query name and edit the query further or save it, as desired.

Step 18. Optionally, format the query.

Click the Format Query icon to format the query to make it easy to read.

Step 19. Optionally, add the Query Parameter.

In the Properties tab, click the Query Parameter icon and add the parameter, if the query uses a parameter.

This example does not add a parameter.

Step 20. Optionally, map the columns.

If the query requires mapping columns, click the Custom Mapping icon in the Properties tab.

This example does not map any columns.

Step 21. Save the entity.

When you are finished working with the query, save the entity.

What are the improvements over S3 Version 6?

This is a new process.

Related Articles

Add a Query to an Entity

Create a New Sub Query

Modify a Query or Sub Query

Add a Query with a Join and Map the Columns

Delete a Query

This post is part of the Entity topic. Click here to open the Entity Overview.

#Xelence
#Entity