Designing a View in the Grid Panel
The following sections describe Grid panel tasks. Drawing from the tables you added using the Model panel, use the Grid panel to select which columns to add:
After the columns have been added, you can use the Grid panel to refine column definitions, sorting order, and other attributes:
Listing All Columns from a Table
You can list all columns from a table in the Grid panel. These tables are added to the SQL SELECT statement. You can refine their definitions using the Columns panel.
To add all columns from a table as rows in the Grid panel
1. Click the List Columns button.
The List Columns from Tables dialog box opens, displaying a field on the left showing the available tables, and a field on the right showing the tables you have selected.
2. You can move tables to the right side in one of two ways:
— Click the double-headed arrow button to move all available tables to the right side.
— Select one (or more, using Ctrl-click or Shift-click) of the available tables, and click the right-arrow button to move the selected tables to the right side.
You can move a table back to the left side by selecting it and clicking the left-arrow button.
3. Optionally, if you want to add all columns from tables even if they are already displayed in the Grid panel, uncheck the Skip Duplicate Columns check box.
4. Click OK.
Columns from the tables you selected are added to the bottom of the Grid panel listing. The view editor automatically creates aliases for identical column names, whether they came from the same table or different tables. You can select any alias on the Grid panel and type a new alias for the column.
5. Select the SQL panel and see that the SELECT statement now includes all of the columns you requested.
Adding an Individual Column
You can add individual columns from a table, and then move it to the position you want on the Grid panel.
To add an individual column as a row in the Grid panel
1. Click the Grid tab to open the Grid panel.
2. Click an empty cell in the left column (the Column column).
3. When the cell becomes a bordered field, click the drop-down button on its right and select a table column from the list.
The list includes all columns from all tables in the model.
4. Optionally, clear the check box in the Output column to exclude the item from the view execution results.
Note: You can check or uncheck all check boxes in the Grid Output column at once, using the Select All Outputs or Clear All Outputs toolbar icons, respectively.
Creating an Alias for a Column
You can supply an alias that is the column name that users see when they browse or query the TDV data services you create, so it is wise to assign an alias that makes sense to those users.
Note: If you specify a reserved word as an alias, it is enclosed in double quotes in the SQL statement. For a list of reserved words, see the TDV Reference Guide.
To assign an alias to a column in the Grid panel
1. Click the cell under Alias that corresponds to the column item you want to give an alias.
The editor may have assigned an alias automatically. You can highlight it and type a new alias to replace it.
2. Type an alias for this column in the cell.
Note: Automatically generated SQL from some ODBC clients (Excel and others) needs to be modified if you intend to use the alias to query the data sources.
Changing a Column Data Type using the CAST Function
Changing data types is sometimes necessary because the view might need to be consumed through a client interface that does not support that data type, or perhaps the data type defined in the data source is not optimal for your current needs.
To declare a variable in a SELECT statement
1. Open any view.
2. Select the Grid tab.
3. Select any row with data.
4. Right click and select Function > Convert > CAST.
5. Click OK.
Including a Column in the ORDER BY Clause
In the Grid panel you can add an ORDER BY clause to the SQL statement to sort the results by one or more columns, in ascending or descending order.
Note: If you choose sort order before you choose sort type, Unsorted is the only Sort Type option available.
You should not use OFFSET and FETCH in a TDV view.
To include a column in the ORDER BY clause
1. Click the cell in the Sort Order column for the row, and select a number to indicate the order in which that column is to be sorted (1 for first, 2 for second, and so on).
Note: As you designate more rows, more sort-order numbers become available. If you specify the same number more than once, the editor assigns a distinct sort order number for the other row. Check that the assignments are what you want.
2. Click the cell in the Sort Type column for the row, and select Ascending, Descending, or Unsorted.
Note: If you want to remove a row from the ORDER BY clause, select Unsorted in the Sort By column.
3. Click the SQL tab to look at the resulting ORDER BY clause.
Specifying a GROUP BY Clause
In the Grid panel you can group by an item, an expression, or a restriction involving aggregate functions.
To specify a GROUP BY clause
1. To group by an item, click the cell where the item’s row crosses the Group By column and choose one of these options from the drop-down list:
— None—to omit any GROUP BY clause.
— Group By—to add a clause to GROUP BY the item in this row.
— Expression—to group by an expression.
— Having—to place restrictions (involving aggregate functions) on the rows returned from the GROUP BY clause.
Specifying Criteria on a Column
You can combine multiple criteria in one row using OR logic. Criteria entered in different rows (vertically) are combined using AND logic. A column does not have to be included in the result set to have criteria associated with it.
For example, in an Order table with a total_price column, if you want to retrieve large and small orders, you might enter >100 in the Criteria column, and <10 in the first Or column.
To specify criteria on a column
1. To specify a criterion on an item, click the cell where the item’s row crosses the Criteria column and enter the first criterion in the cell.
2. To specify more criteria on an item (up to a total of four) click the cell where the item’s row crosses an Or column and enter the criterion in the cell.
Including a Function in a SELECT or WHERE Clause
You can specify a function to include in a SELECT statement using a cell in the Column column for the item’s row. You can specify a function to include in the WHERE clause of the SQL statement using a cell in the Criteria column for the item’s row. The functions available are listed in “TDV Support for SQL Functions” in the TDV Reference Guide.
Note: The functions you can add include custom functions. See
Promoting Procedures to Custom Functions for a description of how administrators can promote procedures, which then appear in the drop-down list of functions.
To include a function in a SELECT statement
1. Right-click the cell in the Column section, and select Function > <function type> > <function name>.
2. Specify the input arguments for the function, and click OK.
The function format is provided in the Function Arguments Input window. The function added in this way is included in the SELECT statement.
To include a function in a WHERE clause
1. Right-click the cell in the Criteria section, and select Function > <function type> > <function name>.
2. Specify the input arguments for the function, and click OK.
The function format is provided in the Function Arguments Input window. The function added in this way is included in the WHERE clause.
Declaring a Variable in a SELECT Statement
The variables defined using the following steps are considered “virtual columns,” and they are included in the SELECT statement. Virtual columns help to integrate views and procedures.
Typically, virtual columns are implemented in the following ways:
• As a range of values in the filter criteria
• As a single value so it can be used to call a procedure
Note: XML types are allowed. If you want to specify an XML schema, you must edit the SQL of the view directly and the declaration of your column needs to include the XML schema definition of that value. See the XML example that follows the steps.
To declare a variable in a SELECT statement
1. Right-click a Column cell, and select Declaration.
2. In the Add Declaration window, in the Parameter Name field, supply a unique name for the variable.
3. Specify the type of the variable in the drop-down list.
4. In the Default Value field, specify the default value for the variable.
5. Click OK.
The value you defined should appear in the Column cell in the form:
{DECLARE <variable_column_name> <data_type> DEFAULT <default_value>}
Example of How to Modify the Declaration of the XML Virtual Column
To specify an XML schema, you must edit the SQL of the view directly and the declaration of your column needs to include the parts of the XML schema definition that are relevant for that value. After you edit the SQL of the view directly, you will not be able to edit your SQL model. The following shows the XML schema definition associated with a single virtual column:
{DECLARE Request_decl
<xs:complexType>
<xs:sequence>
<xs:element name="ReqId" nillable="true" type="xs:int"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>') DEFAULT NULL
}
{DECLARE inputXML_decl XML DEFAULT null} inputXML,
...
Example that Uses Procedures
This example uses two views and the procedure that is distributed with the product.
requires_prod_id View
-- This view requires that a single product ID to be specified, so it can be used to join with
-- a lookup procedure.
SELECT
a.ProductID,
{ DECLARE a_prod_id INTEGER } a_prod_id,
a.ProductName,
b.ProductDescription
FROM
/shared/examples/ds_inventory/tutorial/products a INNER JOIN
/shared/examples/LookupProduct(a_prod_id) b
ON a.ProductID = a_prod_id
wrap_prod_id View
SELECT
ProductID,
a_prod_id,
ProductName,
ProductDescription
FROM
/shared/DEMO/VirtualColumn/for_procedures/requires_prod_id
WHERE
a_prod_id = 10
Example that Uses a Range of Values
This example uses two views and a procedure.
proc_wrap_prod_id Procedure
PROCEDURE proc_wrap_prod_id(
IN prod_id_begin INTEGER,
IN prod_id_end INTEGER,
OUT result CURSOR (
OrderID INTEGER,
ProductID INTEGER,
Discount NUMERIC(12,2),
OrderDate DATE,
CompanyName VARCHAR(50),
CustomerContactFirstName VARCHAR(30),
CustomerContactLastName VARCHAR(50),
CustomerContactPhone VARCHAR(30)
)
)
BEGIN
OPEN result FOR
SELECT
OrderID,
ProductID,
Discount,
OrderDate,
CompanyName,
CustomerContactFirstName,
CustomerContactLastName,
CustomerContactPhone
FROM
/shared/examples/ViewOrder ViewOrder
WHERE
(ProductID >= prod_id_begin)
and (ProductID <= prod_id_end)
;
END
requires_prod_id View
SELECT
products.ProductID,
{ DECLARE prod_id_begin INTEGER } prod_id_begin,
{ DECLARE prod_id_end INTEGER } prod_id_end,
products.ProductName
FROM /shared/examples/ds_inventory/tutorial/products products
WHERE
(ProductID >= prod_id_begin)
and (ProductID <= prod_id_end)
view_wrap_prod_id View
SELECT
ProductID,
ProductName
FROM
/shared/DEMO/VirtualColumn/range_of_values/requires_prod_id
where
prod_id_begin = 5
and prod_id_end = 15