SELECT Section Notebook Pages

Data Access User Manual > Rogue Wave Views Data Access Gadgets > Data Source Gadgets Reference > IliSQLDataSource > 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

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.

Version 6.3