|
|
|
|
|
- Overview
- How to add an item into the current select query?
- Adding an item into the current query
- Performing a full-text search
- Adding a count item into the current query
- Adding a "calculated" item into the current query
- How to modify a "calculated item" in the current query?
- How to rename an item in the current query?
- How to display/hide an item in the current query?
- How to delete an item from the current query?
Items used in a select query
A query created in the query editor can contain several types of items: - items of the analysis to which the query is attached.
These items correspond to the items of the data files described in the analysis. You have the ability to apply calculations to these items. Remark: You have the ability to use a full-text index item: in this case, the query will perform a full-text search. - items of a query found in the current project. These items correspond to the items used in the queries included in the application.
- calculation items. This type of item is used to perform a calculation on the values of an item: sum, average, maximum and minimum.
- count items. This type of item is used to count the number of records found in the query or the number of records found in a grouping of records.
- "calculated" items. This type of item is used to add to the query result an item whose value corresponds to a calculation formula on the items. For example, PriceBT * VATRate.
The items taking part in a query are selected when creating the query. However, you have the ability to add, modify or delete the items used in a query. These operations are described in the following paragraphs. Remark: The items whose value is displayed in the result of the query are called result items. How to add an item into the current select query? Adding an item into the current query To add an item into the current query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the left section, double-click the item to add. The added item is automatically displayed in the middle section.
- Validate.
Remark: An item can also be added into the current query from one or more items described in the data model editor: to do so, Drag and Drop the selected items from the "Analysis" pane to the query editor. Performing a full-text search To perform a full-text search: - Select "Query description" in the context menu of the query. The query description window appears.
- In the left section of the window, double-click the "FullText_Index" item to add. The description window of the search is displayed.
- Specify the parameters of the "full-text" search to perform:
- the index used.
- the search value (value or parameter). If the value is described in the query, a wizard allows you to enter all the necessary conditions on the search value.
- the sort of query result according to the relevance search subscript for each record.
Remark: only the records whose relevance subscript is greater than 0 are stored in the result of the query.
Adding a count item into the current query To add a count item into the current query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the right section, expand the "Count" button.
- To count:
- the number of values for the selected item in the query result: select "On the selected item".
For example, this option can be used to find out the number of customer who have a cell phone ("CellPhone" item filled). - the number of values for another item in the query result: select "On a new item" and the corresponding item.
- the number of records found in a file or in a query (according to the selection conditions of the current query): select "On a file or query".
For example, this option can be used to find out the number of products found in the "Product" file.
Remark: Regardless of the count operation performed, you can specify whether the duplicates must be taken into account (or not) by the count operation (Count(Distinct) in SQL. Adding a "calculated" item into the current query To add a "calculated" item into the current query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the left section, expand the "Calculated item" button and select "New calculated item". The description window of a calculated item is displayed. This window allows you to enter the SQL code corresponding to the calculation to perform. In this window, you can:
- handle the items found in the files of the current analysis and the items found in the queries of the current project.
- enter SQL statements. The list of available SQL statements is displayed. All you have to do is double-click a statement in order to display it in the code section.
- enter WLanguage functions. The list of available WLanguage functions is displayed. All you have to do is double-click a function in order to display it in the code section. To use a WLanguage function, its name must be preceded by "WL.".
- nest the WLanguage functions and the SQL code used.
- Specify the mathematical formula of the item and validate. The item is automatically added.
Remark: Using parameters in the code of the calculated item. To use parameters in the code of the item, the name of the parameter must be enclosed in braces. For example: LEFT(MyItem, {ParameterName}) Caution: The result may be different in test mode and at runtime. Indeed, in test mode, the value of the parameter is always passed in string format. How to modify a "calculated item" in the current query? To modify a "calculated item" in the current query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the middle section, select the calculated item to modify.
- In the left section, click the "Calculated item" button and select "Modify the calculated item". The description window of a calculated item is displayed.
- Modify the formula and validate.
Remark: Using parameters in the code of the calculated item. To use parameters in the code of the item, the name of the parameter must be enclosed in braces. For example: LEFT(MyItem, {ParameterName}) Caution: The result may be different in test mode and at runtime. Indeed, in test mode, the value of the parameter is always passed in string format. How to rename an item in the current query? To rename a result item in the current query (alias creation): - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the middle section, select the item to rename.
- In the right section, click the "Display/Rename" button.
- Enter the new name of the item and validate. The item is automatically renamed.
How to display/hide an item in the current query? To display/hide an item in the result of the query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the middle section, select the item to display/hide.
- In the right section, click the "Display/Rename" button.
- Answer "No" to the question "Display this item?".
Remark: To display/hide an item in the result of the query, you can also: - open the context menu of the requested item (right mouse click).
- select "Display item" or "Don't display".
How to delete an item from the current query? To delete an item from the current query: - Open the query description window: on the "Query" tab, in the "Edit" group, click "Description".
- In the middle section, select the item to delete and press the Del key. The item is automatically deleted from the query.
Remarks: - Deleting an item also deletes the selection conditions defined for this item.
- To delete an item, you also have the ability to select "Delete the item" in the context menu of the item.
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|