Introduction
Queries are the backbone of the entities that are used to create forms, which are then used to pull data from the database and display it on the LOB application. Without queries, the proper information would not make it from the tables to the final application.
Prerequisites
An entity must already be created to begin this process.
|
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 |
| 8 |
Add no lock or format the query, if desired |
| 9 |
Execute the query, if desired |
| 10 |
Retrieve parameters, if applicable |
| 11 |
Select the Data Type for each parameter, if applicable |
| 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.
Every query must belong to a group. If no appropriate group for your query exists, you have to create one.
Step 3. Name the group, then click OK.
Give the group a descriptive name so it’s always easy to find.
Tip: 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 Type options 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.
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.
This is the line of SQL code that executes operations like retrieving data.
Step 8. Add no lock or format the query, if desired.
Add no lock allows the app to ignore any locks on the data and read whatever data is available at the time of query execution. Formatting the query makes the query easy to read.
The lock icon adds no lock to the query. The paragraph icon formats the query. When you are finished adding the query, click OK.
Step 9. Execute the query, if desired.
Click the Execute Query icon to execute the query with all columns for the table. This tests the query.
Click the x icon to close the execution window.
Step 10. Retrieve parameters, if applicable.
Click the Get Parameters icon and Xelence will display any query parameters that need to be mapped.
Step 11. Select the Data Type for each parameter, if applicable.
Select the correct data type for each parameter. For example, SSN is a string variable, customer_id is an int variable, etc.
This example does not map parameters.
Step 12. Save the entity.
What are the improvements over S3 Version 6?
The process is identical.
Related Articles
Add a Query to an Entity with the Query Builder
Create a New Sub Query
Modify a Query and 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