Query Results

About Query Results

The following topics describe available actions related to query results.

Export a Query Result Set to a File

About This Task

Note that, when you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user; however, if you export the unformatted query result set to a file or dataset, the exported date values will be unformatted.

Procedure

For the Select query for which you want to modify the output mode, access the Design workspace.
In the upper-right corner of the workspace, select , and then select Export to a File.

The Export to a File window appears.
In the Please provide a File Name box, provide the file name.
In the drop-down list, select the desired file type, and then select Export.
The exported file will download via your browser.
Save the file to the desired location.
The query result set has been exported to a file.

Export a Query Result Set to a Dataset

About This Task

A query can be exported as a dataset, a fixed set of information that is stored as a Catalog item.

Note: When you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user; however, if you export the unformatted query result set to a file or dataset, the exported date values will be unformatted.

Procedure

For the Select query for which you want to modify the output mode, access the Design workspace.
In the upper-right corner of the workspace, select , and then select Export to a Dataset.
The Save As window appears.
In the folder hierarchy, navigate to the folder in which you want to save the query.
In the Name box, enter a name for the dataset. If you select the same name and location as an existing dataset, that existing dataset will be overwritten when you save.
The Caption box is populated automatically with the value that you entered in the Name box.
In the Description box, you can enter a description for the dataset. This is not required to save the query.
The Of type box is populated automatically with the Catalog item type.
Select Save.
The query result set has been exported to a dataset.

Sort the Query Results

Before You Begin

When defining sort criteria for a query, note that:

The sort criteria determines the default sort order for query results. After the results are displayed, users can modify the sort order in the results grid.
If no sort criteria has been defined, the query results will not be sorted in any particular order by default. Users can still modify the sort order in the results grid.
Any sort criteria that is defined for numeric columns in a query will be applied to stored values (vs. displayed values) regardless of whether the query is running in formatted or unformatted mode.
Unlike the sorting that you can apply directly to the results, sorting preferences that you define in the Design workspace are stored with the query itself and are applied each time you run the query.

For example, according to the following image, the Asset Installation Date will be sorted in Ascending order:

These sort options will affect the query results, as shown in the following image:

Note: The sort order that you specify within the query definition will be saved as the default layout for the results.

About This Task

For any query, you can define criteria to determine how the results will be sorted by default.

Procedure

For the query whose results you want to sort, access the Design workspace.
In the Conditions section, in the Sort cell for the field that you want to sort, select Ascending or Descending. By default, the Sort Index cell for the first field that you sort displays a 1, indicating that this field will be used as the primary sort value.
If you want to set a secondary sort value, in the Sort cell of the appropriate field, select Ascending or Descending. By default, the Sort Index cell for the second field that you sort displays a 2, indicating that this field will be used as the secondary sort value.
Repeat these steps until you have defined all the sort values you want.
After you run the query, the columns for which you defined a sorting preference will be sorted in the specific order.

Modify the Value in the Field Cell

Procedure

Access the Design workspace.
In the Conditions section, in the grid, in the Field cell for the column whose value you want to define, select the gray button in the right side of the cell.

The Advanced section of the Expression Builder window appears.
In the text box, enter the text you want to appear in the results, surrounded in single quotation marks.
Select Done.
In the page heading, select .
The Expression Builder window closes, and in the grid in the Conditions section, the word Creator appears in the Field cell.

Remove the Limit on the Number of Results

Procedure

For the query for which you want to remove the limit on the number of results that are returned, access the Design workspace.
In the heading of the Conditions section, clear the Limit Results check box.
The Limit Results to Top box is disabled, and the number that appeared in it is removed.
In the content heading, select .
The query results are displayed, and the number of results that are returned is no longer limited.

Modify the Output Mode of a Select Query

Procedure

For the Select query for which you want to modify the output mode, access the Design workspace.
In the grid in the Conditions section, depending on which mode you want to use, select or clear the Formatted check box as needed.
The Limit Results to Top box is disabled, and the number that appeared in it is removed.
In the content header, select .
The query is run in the selected mode, and the results appear.
Note: When you run a query in unformatted mode, the results will still display formatted date values. Date values will always be displayed in the local time for the user.
Tip: You can save the query to save the selected mode. The next time you run the query, it will use the mode that was selected when it was last saved.

Display Unique Records Only

Procedure

For the query for which you want to display unique records only, access the Design workspace.
In the grid in the Conditions section, select the Unique Records Only check box. Note that, when you are using Oracle schema, if a text field is exists in a query and the Unique Records Only check box is selected when the query is run, an error message will appear. If you are using an Oracle database and want to run a query with a text field, do not select the Unique Records Only check box.
The Limit Results to Top box is disabled, and the number that appeared in it is removed.
In the content heading, select .
The query is run, and the results will include only unique records.

Limit the Number of Results

Procedure

For the query for which you want to define the number of results that are returned, access the Design workspace.
In the Conditions section heading, select the Limit Results check box.
In the Limit Results to Top box, enter the number of results you want the query to return.
In the content header, select .
The query results are displayed according to your selection in the Records box. For example, if you enter 20 in the Records box, the query will return only the first 20 records that meet the other query criteria.