Introduction
This is a way to create a temporary table by joining information from columns in two already existing tables. This allows you to create forms with the applicable information in them. The steps are very similar to creating a new query.
Prerequisites
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 |
| 5 |
Select the Query Type, then click OK |
| 6 |
Enter the query details |
| 7 |
Enter the query, then click OK |
| 8 |
Retrieve parameters, if applicable |
| 9 |
Select the Data Type for each parameter, if applicable |
| 10 |
Refresh the columns |
| 11 |
Map the columns, then click OK |
| 12 |
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 or you can use an existing group to add a new query.
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.
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.
The Query types are:
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 system know what type of query it will be executing.
In almost all cases, the type will be SelectQuery. Choosing the wrong query type could cause the execution to fail. In this case, the Query Type is SelectQuery.
The other types of query are:
ScalarQuery: Returns a single value, such as an average.
SubSelectQuery: Embeds within another query.
NonQuery: Executes an insert, update, or delete statement.
Step 7. Enter the query, then click OK.
This is the SQL statement that produces the joined temporary table.
Give the tables aliases to make queries easier to manage. In this example, sgt_Order is aliased as 'a' and sgt_Customer is aliased as 'b'. It is used in the main query of the order lookup.
Step 8. Retrieve parameters, if applicable.
Step 9. Select the Data Type for each parameter, if applicable.
Select the correct data type for each parameter. For example, SSN is a string.
Step 10. Refresh the columns.
In the Properties section, click Custom Mapping and click the Refresh Unmapped Columns icon to display the unmapped column names list.
Step 11. Map the columns, then click OK.
Enter the Entity Field for the columns you want to use in the application. Incorrect or missing mapping will not allow the columns in the joined table to function properly.
These columns all come from the table of ObjCustomer, the customer table. Each table column is mapped to the entity field. For example, mapping for the ssn column is objCustomer.Ssn.
Step 12. Save the entity.
#Xelence
#Entity