Create a New Sub Query

By Xelence Documentation posted 05-27-2021 10:30

Introduction

The purpose of a sub query is to return values from a related table only when the user requests those specific values. This is important because it improves performance by not querying a table when there is no need.

Prerequisites

The parent query must already exist.
The parent query must be aliased.

	Quick Steps
1	Search for and open the entity
2	Open the Queries section, then add a new group, if required
3	Name the group, then click OK
4	Add a new query to the group
5	Select the Query Type, then click OK
6	Enter the query details
7	Enter the query, then click OK
8	Save the entity

Detailed Steps

Step 1. Search for and open the entity.

Step 2. Open the Queries section, then add a new group, if required.

This is where the entity manages its queries. Click the + icon to add a new group.

Every query must belong to a group. If no appropriate group for your query exists, you have to create one. You can also use any existing group to add a new query.

Open the order entity. Open Queries section and click the + icon to add a new group.

Step 3. Name the group, then click OK.

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.

Step 4. Add a new query to the group.

Click the + icon to add a new query.

Step 5. Select the Query Type, then click OK.

This determines how you will build the query in Xelence.

This example uses Query.

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. For this you can refer to Add a Query to an Entity with the Query Builder process for more details.

Step 6. Enter the query details.

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.

The Query Type lets the application know what type of query it will be executing.

Name your new query. Choose the Query Type. All sub queries are SubSelectQuery type.

Name your new query. Choose the applicable Query Type. All sub queries are SubSelectQuery type.

Step 7. Enter the query, then click OK.

Your sub query can be anything you want it to be, but it must join the sub query (child) to a parent query.

Enter the SQL code for your sub query.

Give the tables aliases to make queries easier to manage. In this example, sgt_Customer is aliased as 'a' (used in the main query of the customer lookup) and sgt_Order is aliased as 'b' (used in sub query of the customer lookup).