Creating tables using SQL

You can create new tables in a data module that are based on a custom SQL syntax. The SQL is executed against a source that is already in the data module.

If the SQL validation is successful, the table is populated with a set of projected column names and rows of data.

The supported types of SQL include Cognos SQL, native SQL, and pass-through SQL. For more information, see Supported SQL types.

Procedure

  1. From the data module context menu Context menu icon, select Create table using SQL.
  2. In the table editor, type the table name.
  3. From the SQL type drop-down menu, select the type of SQL to use.
  4. From the Source drop-down menu, select the source to associate the table with. For data server connections, select the connection name. For other types of sources, select the source location, which is either Team content or My content.
  5. In the Expression box, type or paste the SQL syntax for your table. The syntax is executed only against the source that you selected in the previous step.

    The expression editor provides the following syntax validation and editing options:

    • Validation icon in expression editor - Validate the syntax. You can validate the whole statement, or only selected segments of code.
    • Preview icon in expression editor - Preview columns and rows in your projected table. If the syntax is not correct, the columns are not displayed.
    • Help icon - View descriptions of functions, and examples of their usage.
    • Comment icon in expression editor - Insert the cursor anywhere in a line of code and select this button to comment out the entire line. To comment out multiple lines of code, select the lines and select this button. The comment string (--) is added at the beginning of each selected line.
      Tip: To comment out sections of code, manually enclose the text between the following strings: /* and */
    • Prettify code icon in expression editor - Apply formatting to the code.
    • High-contrast icon in expression editor - Use high-contrast mode.
    • Change the font size.
  6. Click OK to save the table.

    You can save the table even if it contains syntax errors, and edit the syntax later. However, you cannot modify any aspect of the SQL table, or view its data in the grid, until the table is successfully validated.

Results

The table name appears at the top of the data module tree. To edit the table SQL, from the table context menu, click Edit SQL table. You can also edit a column expression in an SQL-based table. However, subsequent updates to the original SQL statement might overwrite the updated expression.

What to do next

You can use and model SQL-based tables in the same way as other data module tables. For example, you can create relationships between this type of tables and other tables. You can also create calculations and navigation paths that include columns from these tables.