Querying A Table In Reference Data Management

Saved filters

Allows you to manage saved filters within the Query dialog.

• Update - Allows you to modify existing saved filter by updating the options.
  To update, select the desired filter, edit the options and click Update from the Acrion
  Select the desired filter, edit the options, click Action > Update to update the saved filter.

• Delete - Allows you to delete saved filter. Select desired filter, click Action > Delete to delete the saved filter.

• Save New - You are only able to save new filter once you have selected a profile and a table. Upon saving, the filter will be added to the list of saved filters for future use.

Action

Contains the following actions to manage the saved filters:

• Update - Allows you to modify existing saved filter by updating the options.
  To update, select the desired filter, edit the options and click Update from the Action dropdown.

• Delete - Allows you to delete saved filter.
  To delete, select the desired filter, click Delete from the Action dropdown.

• Save New - You are only able to save new filter once you have selected a profile and a table. Upon saving, the filter will be added to the list of saved filters for future use.

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.

Search Type

There are two types of search that you can select as follows:

• Standard - Query the results from a table based on the Reference Data Management profiles in the system. This query supports the following operations on query result:

  • Insert Row

  • Edit

  • Delete

  • Export

  • Import

• Join Table - Query the results from join tables based on the Reference Data Management profiles in the system, supports following operations on query result:

  • Export

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. You select from one table in the list to perform your query on.

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. You select from one table in the list to perform your query on. The alias of the selected main table will always be t1 which will be used for Join Constraints.

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:

• JOIN

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• CROSS JOIN

Open

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 remove and add the columns at your choosing to be displayed in the query result. Clicking on the All button will select all the columns in the table.

All columns are selected by default.

Data Set Size

This option appears once you have selected a profile and a table. The value in this field controls the maximum row count per data set. The queried data is fetched from the Platform in data sets of configurable size. A large size typically results in a faster query but it may take longer to display the first data set.

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.

Column

This is a column in the selected database table.

Operator

This is a comparison operator that is applied to the values in the specified column and the value.

The available operators are:

• equals

• not equals

• like

• not like

• greater than

• greater than or equals

• lesser than

• lesser than or equals

• between

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.

Column

This is a column in the selected database table which data needs to be sorted.

Direction

You can select to view the query results in the following manner:

• Ascending - Query result is sorted from lowest to highest value

• Descending - Query result is sorted from highest to lowest value