Creating the Definition Using Views Studio
The IliSQLTable can be defined interactively in Views Studio through the IliSQLDataSource object inspector.
The SQL Data Source Inspector
Within SQL Data Source inspector, you can:
Specify the database tables to be referenced by the FROM clause (the Add Tables menu item).Specify the joins between tables when multiple database tables are added. These joins can be specified by dragging a column from one table to a column in another table. This results in a condition of the form Table1.Column1 = Table2.Column2 being added to the WHERE clause of the SQL SELECT statement.Specify the columns of the IliSQLTable object. These columns are derived from the columns of the database tables in the following way: drag a column belonging to a database table and drop it over the SELECT area in the inspector. This will create a new column in the schema of the IliSQLTable object. Alternatively, the SELECT area in the inspector contains an undefined column that allows you to type in the definition of a new column. The rows labeled Select and From are used to specify how the IliSQLTable column relates to the database table. In the From row, enter the name of the database table from which the column is derived. In the Select row, enter the name of the database table column from which the column is derived.
If you want to define an IliSQLTable column that is derived from multiple database columns, leave the From row empty and, in the Select row, enter an SQL expression that computes the column value (for example, PRICE * QTY).
Specify the WHERE clause by typing the selection criteria in the Where row. If you enter conditions for multiple columns, these conditions will be combined with an AND logical operator in the SELECT statement. Once you enter a condition in the Where row, a new row labeled Or appears. In the Or row, you can type a new set of conditions that will be combined, via an OR logical operator, with the Where row conditions. There can be any number of Or rows.If the SELECT statement contains a GROUP BY clause or if it contains aggregate functions such as COUNT or SUM, enter the appropriate operations in the Operation row. Note that in this case, all columns must have a specified operation and that conditions should be entered in the Having row instead of the Where row.Specify the ORDER BY clause in the “Order By” row.Specify other column properties in the Datatype, Look, and Mapping notebook pages.Specify the parameters used in the WHERE clause in the Parameters page.If duplicate rows should be removed from the result produced by the SELECT statement, the Distinct property should be set to Yes.In addition to performing an SQL SELECT statement, an IliSQLTable object is able to forward user updates to the database. This involves generating SQL UPDATE, INSERT, and DELETE statements on-the-fly. Note that when more than one database table is added to a data source, only one of the database tables can be updated in this way. Ensure that the updatable Table property contains the database table name that will be changed through the IliSQLTable object.At this point, the IliSQLTable is structurally defined. This means that its schema is defined and the process that it should use to compute its rows from the tables in the database is also defined.
Published date: 05/24/2022
Last modified date: 02/24/2022
