ComponentOne ADO.NET DataExtender
C1DataView Relations / Creating Table and Column Aliases
In This Topic
    Creating Table and Column Aliases
    In This Topic

    You can create aliases to make it easier to work with table and column names. The table and column occurrences in a definition statement are differentiated by their aliases. Aliases come in handy when:

    For example, you can create an alias o for a table name Orders, and then refer to the table as o throughout the rest of the query.

    The following statement shows an example of table and column aliases:

    SELECT o.OrderID AS [Order ID], o.OrderDate AS [Date of Order], od.* 
    FROM Orders AS o JOIN [Order Details] AS od ON o.OrderID = od.OrderID
    
    

    In this statement the Orders table has been given the alias o, and the Order Details table has the alias od. The OrderID column has the alias Order ID while the OrderDate column has the alias Date of Order.

    To create this statement using the C1ViewSetDesignerForm, complete the following steps:

    1. Right-click the C1DataViewSet component and select Edit from its context menu. The C1ViewSetDesignerForm appears
    2. Click the Add View button. The Add View dialog box opens.
    3. Select a parent view from the right pane, and under Select a child view to add, select Composite view from the list.
    4. Click Add.

      The C1DataView Definition Statement Builder opens.

    5. Click the Tree View button, this represents a view definition as a tree and allows you to add tables.
      Note: Alternatively, you can access the C1Data View Definition Statement Builder by clicking the ellipsis button next to the Definition property.
    6. Click the Add Child Table button to open the Add Child Table dialog box and select Orders from the list of available tables. Click Add, and then Close.
    7. Under Table properties enter o in the Alias text box.
    8. To create the first part of our definition, select o.OrderID from the Column drop-down list.

      The column qualifier in the SELECT column list can have one of the following forms:

      • * denotes all columns of all base DataTable(s). If it's defined it must be the only column qualifier in the column list.
      • [<table alias>.* denotes all columns from the specified base DataTable.
      • [<table alias>.]<column name> [AS] [<column alias>] denotes the specific DataColumn with optional column alias, with optional indication of the base DataTable to which this column belongs.
      • <expression> [AS] <column alias> defines a calculated column, whose value is determined by evaluating the specified expression. The expression can include constants, column references, arithmetical, logical and comparison operations, and IS [NOT] NULL constructions, <match_expression> [ NOT ] LIKE <pattern_expression> [ ESCAPE <escape_character> ] constructions, and subexpressions can be grouped by means of parentheses. Column references of the expression must indicate columns from the base DataTable(s), but not the columns from the SELECT list.
    9. Enter Order ID in the Alias column to give the OrderID column of the o table the alias Order ID.

      Note: The table automatically changes to o in the Table column.

    10. To add another column to our C1DataView, select o.OrderDate from the second row of the Column column.
    11. Enter Date of Order in the Alias column.
      • Note: The table automatically changes to o in the Table column.
    12. In this example, you will create a JOIN to the Order Details table. Click the Add Child Table button.
    13. Select Order Details from the Select a child table to add list, and enter od in the Table Alias text box.
    14. In the Column column, select od.* from the drop-down list. This allows you to select all of the columns from the Order Details table for our C1DataView.

      The table automatically changes to od in the Table column, and our definition is complete:


    15. Click OK, and then close the Add View box.
      The new Orders view is added to the C1ViewSetDesignerForm, and the definition is assigned to the Definition property.
    See Also