Tables Tab - DB (Database) Definitions
The first tab opened with a new DB Definition is the Tables tab.
At least one (1) table will need to be selected - regardless of whether a Source definition or a Destination definition.
The upper pane contains a list of available tables contained within the database defined by the DB Connection that was entered on the initial screen, and the lower pane will display tables that have been selected for inclusion in this query. Examples of two (2) different databases and their tables follow, illustrating how different they can be.
Columns
Column Name | Description |
|---|---|
Available Tables Table | Displays the tables (datasets) contained in the database that is referenced by the DB Connection that was selected. |
Table Name | Displays the table (dataset) alias for the listed table. If there is no alias, the table name will be repeated. |
Selected Tables Table | Displays the tables (datasets) that have been selected from the list of available tables. |
Table Name | Displays the table (dataset) Statelake alias for the selected table. |
SQL Alias | Displays the table (dataset) SQL alias for the selected table. If there is no alias, the table name will be repeated. |
Join Type | The type of SQL join to be applied. This will only have a value if there is a table join. |
Buttons
Button Name | Description |
|---|---|
OK | Click to save any join changes you have made. All changes will be lost if you do not click the OK button. To save, there must be at least one (1) Field selected. |
Cancel | Click to Cancel any join changes you have made. |
Allow Magic Links | This box is ticked by default. It activates Magic SQL and allows the generation of SQL behind-the-scenes. However, where only a single dataset table is identified for the definition, and depending on the number of transactions involved, this may slow down processing. Applies only to the parent dataset where multiple have been selected. Please refer to Introduction to Magic SQL for further information. |
Distinct | Tick to activate this SQL feature, which suppresses duplicates and will only return unique values from the query. |
Row Limit | Enter a value to set the Row Limit. See below for more information. |
Row Limit
This field accepts integer values only, and tells Statelake how many records to return from the query, starting at the top of the selected records. Entering a value into this field at table level, limits the search to find and return the number of rows that are specified in this field. The Row Limit is to only be applied on the parent table.
This field is also referred to as the TOP X field - X being the number of records starting at the top of the retrieved records that meet the selection criteria. TOP is an SQL specific keyword.
If a sort sequence has been entered, then the retrieved records are sorted before the selected TOP X records are returned. Where no sort has been requested as sin this example, the records are returned in a random order and the TOP X records will be taken from this random list.
For instance, entering a Row Limit of 1 will cause Statelake to find all of the records that met the specified criteria in the parent table as per the SQL script, and after performing any requested sort, will then return the first record only. Where there is no particular sort specified, the results will be random and only the first row in this random order will be returned.
When used in conjunction with the Execution settings on an Action, setting the Row Limit to one (1) to process only one (1) record at a time, can preserve data integrity by ensuring that previous records have been safely processed before an error may occur to the current record - so it is not part-way through a group of records.
However, by entering a Row Limit of 10 on the parent table, Statelake would find all of the records that met the specified criteria as per the SQL script, and after performing any requested sort, would then return the first 10 records only. Where there is no particular sort specified, the results will be random and only the first 10 rows in this random order will be returned.
Regardless of the Row Limit set on the parent, the child will return all records that met the specified criteria.
For example, the following has no Row Limit set.
But now has a Row Limit set as twenty (20).
