Creating a DBMS SQL Flow

How to:

A DBMS SQL flow allows you to enter dialect-specific SQL SELECT statements for extracting data from a relational database. Dialect-specific SQL is the SQL specific to one particular relational database, such as ORACLE or MS SQL Server.

You can type in the SELECT statement, or import it from a pre-existing .sql file.

A DBMS SQL flow only contains a specialized SQL object where you select the DBMS adapter and connection, and one or more target objects. Synonyms for sources are neither required nor used. Tables must be identified using the same names as in the database.

While source transformations cannot be used, target transformations, functions, variables, validates, target properties, and record logging can be used. The procedures for using these capabilities are the same for DBMS SQL flows as for more typical data flows.

For information on these procedures, see the appropriate topics earlier in this chapter.


Top of page

x
Procedure: How to Create a DBMS SQL Flow

The following procedure uses the Microsoft SQL Server pubs.dbo.authors sample table, and the Microsoft SQL Server difference scalar function, which is not available in ANSI SQL.

  1. Right-click the application directory where you want the new data flow, select New, and then click DBMS SQL Flow.

    An empty flow appears in the data flow workspace. The DBMS SQL Flow tab opens by default.

    A DBMS SQL Object appears in the center of the workspace.

  2. Right-click the DBMS SQL object and select Properties.
  3. Select an adapter and connection from the drop-down menus and close the Properties panel.
  4. Right-click the SQL object and select SQL Statement.

    The Select Statement dialog box opens.

  5. Type a dialect-specific SQL Statement, like the following in the Select Statement dialog box.
    SELECT 
      au_fname, au_lname
    FROM 
      pubs.dbo.authors
    WHERE
      difference  (au_lname, 'delay')  > 3

    Select Statement dialog box

  6. Test your SQL by clicking the Test SQL Statement button.

    Test SQL Statement window

  7. Click Close. Then click OK.

    Note: The SQL statement must be a SELECT statement that conforms to the dialect-specific requirements of the adapter.

  8. Optionally, you can import an SQL statement from an existing .sql file by clicking the Import button, selecting an SQL file, and clicking the Select button. The SQL file must be in one of your Application Directories.

    Select Synonym window

  9. Optionally, you can export SQL statements as .sql files for later use by clicking the Export button. Select a Server and Application Directory from the drop-down menus, enter a File Name, and click OK.

    Remote File Name window

  10. Drag a data target into the workspace. You can also right-click in the workspace, select Add Target, then Existing or Add Target, and then click New.
  11. Click the Save button on the Quick Access Toolbar.
  12. Enter a name for the flow in the File Name field and click OK. You can also select a Server and Application Directory from the drop-down menus.
  13. Run the flow.

iWay Software