Specifying structures of virtual tables

When the data structure and metadata of a database table are different, use virtual tables in virtual data sources to edit or convert the metadata in a convenient way.

1. On the second page of the Create New Virtual Table wizard or on the Structure tab of the Properties window, use the Fields table to specify the structure of the virtual table.
2. To edit a column name, click a cell in the Name column and enter a new name.
3. To specify a different data type, click a cell in the Type column and select a data type from the list. Available options are the following:
   • INTEGER can be used to store signed whole numbers between -2 147 483 648 and 2 147 483 647.
   • BIGINT can be used to store large signed whole numbers between -9 223 372 036 854 775 808 and 9 223 372 036 854 775 807.
   • DOUBLE can be used to store large numbers with a floating decimal point.
   • CHAR is used to store fixed-length string of characters. For example, you can use it to store employee phone number which is generally fixed in length.
   • VARCHAR is used to store variable-length string of characters like employee name.
   • DECIMAL is used to store the fixed precision and scale numbers. This data type should be used for precise values, such as currency.
   • DATE is used to store a date only.
   • TIME is used to store a time only.
   • TIMESTAMP is used to store both a date and a time.

   Note: DATE, TIME, and TIMESTAMP data types support different format options that define how the values are displayed in the result set.

   Note: The supported ranges and the precision of values might vary depending on the database you use.
4. In the Length field, specify a maximum number of characters for the VARCHAR type.
5. In the Precision field, specify a maximum total number of digits for the DECIMAL type.
6. In the Scale field, specify a maximum number of:
   • fractional digits for the DECIMAL type
   • digits in the fractional seconds part for the TIMESTAMP or TIMESTAMP WITH TIMEZONE type
7. Optional: In the Comment field, enter a comment for the column in the virtual table.
8. To delete a column from the virtual table, click Remove Column.
9. To restore a deleted column, click Add Column and select the column that you want to restore in the Add column window.
10. To add a calculated column to a virtual table, click the Add Column button.
    1. In the Add column window, create a conditional expression for a new calculated column in the Or, enter an expression here field.
11. To change the position of the columns in the virtual table, use the Move Up or Move Down buttons.
12. Click Next to specify the cache expiration for the virtual table or click Finish to close the wizard.