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:
*Apply
*Close
Button
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