To query a table, you can do one of the following:
click the Get Started button from the initial dashboard page
click the Query button on the result view. A Query dialog will open where you will configure the criteria for your query.
Info!
If a query was performed previously, the last applied query settings will be reloaded. If the settings cannot be fully reloaded, a notification will prompt users to verify and update the values as required.
Opening the Query dialog will discard any uncommitted changes and refresh the Reference Data Profiles if there are any updates.
Full Query dialog with Query Options and Query Expressions
Options | Description |
|---|---|
Saved filters | Allows you to manage saved filters within the Query dialog.
|
Action | Contains the following actions to manage the saved filters:
|
Reference Data Profile | Allows you to select a Reference Data Profile that contains the table you want to query from. For more on Reference Data profiles, you can refer to Reference Data Profile(4.2). |
Search Type | There are two types of searches that you can select as follows:
|
Table | This option appears once you have selected a profile and Standard Search Type. This is a dropdown list of tables configured in the selected Reference Data profile. Select a table from the list to perform your query. |
Main Table | This option appears once you have selected a profile and Join Table Search Type. This is a dropdown list of tables configured in the selected Reference Data profile. Select a table from the list to perform your query. The alias of the selected main table will always be t1 and will be used for Join Constraint. |
Join Conditions | This option appears once you have selected a profile and Join Table Search Type. Add join conditions to combine two or more tables by selecting Table Name, Join Type and configure Join Constraints. The alias of the selected table will be stated next to the Table Name label, for e.g: The screenshot below indicates that t2 will be the alias of the table. The following join types are supported:
 Reference Data Management - Join Condition Dialog Join Constraint is used to specify the conditions for joining tables, for e.g: t1.column_id=t2.column_id. |
Show Columns | This option appears once you have selected a profile and a table. This displays all the column names available in the selected table. You can add or remove columns to customize which ones are displayed in the query results. Clicking the All button will select all columns in the table. By default, all columns are selected. |
Data Set Size | This option appears once you have selected a profile and a table. It controls the maximum number of rows per data set. The queried data is fetched from the Platform in data sets of this configurable size. A larger size generally speeds up the query process but may delay the display of the first data set. Info! Data set is the scope in which query results are displayed and modifications are applied. When switching data set, any uncommitted changes will be discarded. Users switch between data sets with buttons on the footer bar in the query result view. |
Query Expressions | This option appears once you have selected a profile and a table. Query Expressions allow you to filter the query results according to the criteria you configure in this option. Click on New Expression to add one to the query. |
Sort Expression | This option appears once you have selected a profile and a table, defaulted to first column in ascending direction. Sort Expressions allow you to sort the query results according to the criteria you configure in this option. Click on New Sort Expression to add additional expression. |
Query Expressions
Options | Description |
|---|---|
Column | This is a column in the selected database table. Note! Only columns with supported data types are available for selection. |
Operator | This is a comparison operator that is applied to the values in the specified column and the value. The available operators are:
|
Value | This field contains a value that is used in the expression. When between is the selected operator, two value fields are displayed. You can use SQL wildcards such % and _ with the operators like and not like. An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value. A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null. |
Sorting Expressions
Sort Expressions configuration in the Query dialog
Options | Description |
|---|---|
Column | This is a column in the selected database table which data needs to be sorted. Note!Only columns with supported data types are available for selection. |
Direction | You can select to view the query results in the following manner:
|
Querying a Result
On the Query dialog, choose a Reference Data Profile by clicking on the Browse button. Look for and select the profile that has the tables you want to search from using the Select Reference Data Profile dialog.
Reference Data Profile selection dialog
Select a Search Type and a table from the Table dropdown list. The tables configured in the Reference Data Profile will appear here.
Selecting a table from the Query dialog
Select the columns to include or view in the query results and configure the maximum data set value should you require it to be set to a different number.
Selecting columns
You can also add query expressions to refine the result of the query by filtering out certain data. If you did not configure any expressions, the entire table will be retrieved and up to the number set in Data Set Size will be displayed at a time in the query result view.
Add sort expressions to specify sort criteria for retrieving data in ascending or descending order.
Selecting Sort Expression column
Click Apply to display the results in a view. The status bar at the top and the footer bar will show information about the current query configuration. Click the status bar to reveal a list of configured query expressions, if any. From this view, you can perform the following:
perform another query
use the Search Bar to filter
switch to another data set
insert a new row
edit fields
delete rows
refresh the view
export the data set
export the entire table
import data from a CSV file
Info!
Drag and drop columns to reorder them. The columns will be exported in the new order.
Column sorting is allowed for one column at a time. Both string and number sorting are supported, and uncommitted rows will be excluded from the export.
JSON Viewer
The JSON Viewer is available to assist you by displaying JSON data in a more readable form. This is only available for columns that contain valid JSON format.
Follow the steps below to view the JSON data.
In the Query Result View, click the View Value button.
If the column contains a valid JSON format, the Viewing Value dialog contains the following tabs:
Grid - Displays the JSON data in a grid form.
Text - Displays the JSON data in a text form.
For columns that do not contain valid JSON format, the Viewing Value dialog will display the Text tab only.
Viewing Value - Text tab (for invalid JSON data)