SELECT Section Notebook Pages
The SELECT section of the SQL Data Source inspector panel has six notebook pages:
These pages define the criteria for selecting data from the database and for formatting the data in display gadgets.
Note: “Default” in the pages described below refers to what appears when a column is created in the SELECT section by dragging a line from the FROM section. |
Select Page
The Select page is used for:
defining the data source columns in terms of the columns in the FROM section (Select and From rows),specifying operations to compute results (Operation row),defining the sort order in which the data is to be displayed (Order row),establishing selection criteria by which data is retrieved from the database (Where row).
Note: If the Operation row is used for a column, any entries in the Where row for that column must be made on the Having page. |
Label | Description |
|---|---|
Select | Menu: The columns of the data source table in the From row. Default: No default. Explanation: Defines a data source column taken from the table displayed in the From row, or an SQL expression that may include one or more columns. |
From | Menu: Tables defined for the current data source. Default: No default. Explanation: Specifies the table from which the column in the Select row is taken. Mandatory if a column exists in the Select row. |
Operation | Menu: None, Group By, Count, Sum, Avg, Min, Max. Default: No default. Explanation: Performs operations by which rows are grouped and their aggregate values computed. If “Group By” is used in a column, all the other columns must have an operation. |
Order | Menu: No, Asc, Desc (No=random order, Asc=ascending, Desc=descending). Default: No default. Explanation: Determines the order of the rows in the display table. If more than one column is entered, the leftmost column has priority. |
Where | Menu: None. Default: No default. Explanation: Selection criteria applied to the column in the Select row. The selection criteria is used with a logical AND operator and added to criteria in other columns to further restrict the selection of data to be retrieved from the database. Only applies when the Operation row is empty. |
Having Page
The Having page is used for establishing the selection criteria of the data extracted from the database to which the data source is connected, and when the current column has an Operation defined. Use the Select page when no Operation is defined for the column.
Label | Description |
|---|---|
Select | Menu: The columns of the data source table in the From row. Default: No default. Explanation: Defines a data source column taken from the table displayed in the From row or an SQL expression that may include one or more columns. |
From | Menu: Tables defined for the current data source. Default: No default. Explanation: Specifies the table from which the column in the Select row is taken. Mandatory if a column exists in the Select row. |
Operation | Menu: None, Group By, Count, Sum, Avg, Min, Max. Default: No default. Explanation: Performs operations by which rows are grouped and their aggregate values computed. If “Group By” is used in a column, all the other columns must have an operation. If an operation is used without “Group By” the entire table is used to compute the value. |
Having | Menu: None. Default: No default. Explanation: Selection criteria applied to the column in the Select row. The selection criteria is used with a logical AND operator and added to criteria in other columns to further restrict the selection of data to be retrieved from the database. Only applies when there is a value in the Operation row. |
Datatype Page
The Datatype page is used for defining the type of data that can be entered in the column.
Label | Description |
|---|---|
Name | Menu: None. Default: No default. Explanation: Each column must have a name by which it can be attached to a gadget. This name is automatically taken from the database and appears at the top of the column. This row is used to change this name. While the name given by the system can be automatically changed by adding a prefix to distinguish it from other columns having the same name in other tables, the name entered here will not change. The title at the top of the column in the SELECT section is replaced by the one entered here. |
Type | Menu: String, Long string, Boolean, Byte, Integer, Float, Double, Decimal, Date, Time. Default: As defined in the database schema. Explanation: The type of data that can be entered in the cells of the column. |
Length | Menu: None. Default: As defined in the database schema. Explanation: The number of characters that can be entered in the cells of the column. |
Null | Menu: Yes, No. Default: As defined in the database schema. Explanation: Yes = The cell can remain empty. No = The cell cannot remain empty. |
Default | Menu: None. Default: No default. Explanation: The data that appears in a cell when it is added to the table. |
Retrieve | Menu: Yes, No. Default: No default. Explanation: Yes = Column is an element of the data source. No = Column is not an element of the data source and will not appear in the result. Is used only with selection criteria. |
Look Page
The Look page is used to define how data entered in the column will appear.
Label | Description |
|---|---|
Header | Menu: None. Default: No default. Explanation: Title that will appear at the top of the column when displayed in a table gadget. If left empty, the table gadget uses the Name row on the Datatype page, or, if also empty, the name given by the system from the database schema. |
Label | Menu: None. Default: No default. Explanation: Applies only when the Data Source Assistant is used to create a form. The caption that appears next to the form gadget containing the data for the column. If empty, the label is taken from the Name row on the Datatype page, or, if also empty, the name given by the system from the database schema. (The Header row on the Look page is not used.) |
Format | Menu: Formats corresponding to what is entered in the Type cell on the Datatype page. Default: No default. Explanation: Predefined system and user formats from the menu or a format entered by the user by which data in the column cells will be formatted. |
Mask | Menu: Masks corresponding to how and what data is entered in the Type cell on the Datatype page. Default: No default. Explanation: Predefined by the user for data input in the column cells. There are predefined system masks for date and time. |
Alignment | Menu: Left, Center, Right. Default: Depends on the entry in the Type row on the Datatype page. Explanation: How data in the column cells will be aligned within the cell. |
Width | Menu: None. Default: No default. Explanation: Display width in pixels of the column cells. Can be changed in the table gadget. |
Read only | Menu: Yes, No. Default: No default. Explanation: Yes = Prevents the column cells from being edited. No = Allows the column cells to be edited. |
Visible | Menu: Yes, No. Default: No default. Explanation: Yes = The column is visible. No = The column exists but does not appear. |
Mapping Page
The Mapping page is used for displaying data in a column by referring to data in a column in another table.
Label | Description |
|---|---|
Data source | Menu: Current data sources. Default: No default. Explanation: The foreign data source containing the columns to which the values for the current column are to be mapped. If a foreign data source is specified here, creates a combo box pull-down menu in the cell showing the values in the foreign data source. |
Value column | Menu: Columns of data source selected in data source row. Default: No default. Explanation: The column containing the value to which the current column is to be mapped. |
Display column | Menu: Columns of the data source selected in the data source cell. Default: No default. Explanation: The column associated with the Value column containing the data to be displayed. |
Constrained | Menu: Yes, No. Default: No default. Explanation: Applies only when the value entered in the Value Column and Display column rows is the same. Yes = Can only enter a value that belongs to a foreign data source. No = Can enter any value. |
Completion | Menu: Yes, No. Default: No default. Explanation: Is only in effect when constrained = Yes. Yes = Can enter a combo box list item by typing enough of its initial characters to make it unique, then leaving the cell. No = Cannot enter a combo box list item by typing its initial characters. |
Parameters Page
The Parameters page is used for defining a parameter in terms of data located in a column from any other data source. This parameter can then be used as selection criteria in the Where row of the Select page.
Column | Description |
|---|---|
Parameter | Menu: None. Default: No default. Explanation: The name of the parameter that represents the column from which data is to be retrieved. This name can then be used as selection criteria in a Where row on the Select page. |
Type | Menu: String, Long string, Boolean, Byte, Integer, Float, Double, Decimal, Date, Time. Default: No default. Explanation: Type of the parameter. |
Data Source | Menu: Current data sources. Default: No default. Explanation: Data source from which the parameter will take its value. |
Column | Menu: Columns of data source selected in Data Source column. Default: No default. Explanation: Column from which the parameter will take its value. The column must exist in the data source shown in the Data Source column. |
Callbacks
The SQL Data Source inspector has no Callbacks page. To access this gadget callbacks, open the Callbacks panel by selecting Callbacks from the Tools menu.
Buttons
The SQL Data Source inspector panel has two buttons at the bottom:
ApplyCloseButton | Description |
|---|---|
Apply | Applies changes made in the SQL Data Source panel to the data source. This does not submit a query to the database, which is done by pressing the F9 key when the gadget has the focus or by pressing the “@” button in the navigation tool bar. |
Close | Closes the SQL Data Source inspector panel. |
Published date: 05/24/2022
Last modified date: 02/24/2022
