Adding an SQL statement

This section describes how to configure a SQL Query node for use with SQL statements.

Adding an SQL statement through the wizard

The SQL Query node provides a wizard for generating SELECT statements in SQL (Structured Query Language), the language designed for user with relational databases.

The wizard is a set of tabs each of which represents a clause in a SELECT statement. Within a select, the information needed to generate a clause is encapsulated by each of the tabs.

The steps that follow walk you through the process of creating a SELECT statement for querying using the wizard.

Note: 

The wizard is optimized for use with a screen resolution of 1680 x 1050. If you are not seeing your query statement on the bottom of the wizard screen, try increasing the resolution.

  1. With the SQL Query node selected, move to the SQL tab and then select Run SQL Query.

  2. Click Build.

    You will be on the SQL Query Builder screen on the SELECT tab.

  3. From the row of checkboxes at the top of the wizard screen, select the table or view type that you want to include in your query.

  4. From the list of schemas that displays on the bottom left, double-click to select the schema that contains the data you want to query.

  5. Expand the list to view the list of available tables.

  6. Double-click on each table you want to use in your query and then click the FROM tab. Use the right-pointing arrow to send tables to the list of tables.

  7. Click the WHERE tab. The fields from the tables are available for formulating a condition. Use the operators and values to create your comparison statement. Nesting is available for statements that include multiple conditions.

  8. Use the ORDER tab to sort the output.

  9. Test your statement.

    • Use the Generate Statement button to create the statement.

    • Use the Execute Statement button to test the statement.

    • Use the Return Row Limit to set a limit on the number of rows that will be returned. You can use this option if you think your statement might fetch a large enough number of records to generate an overflow. Limit the output to small number of records so that you can verify that your statement is correct.

  10. When you are satisfied with your query, click Finish. The SQL Query activity node, including the statement you just created, will become part of the process you are currently creating.

Creating your own SQL statement manually

If you have created a process that contains a query similar to the one you want to create, you can import it to use as a template.

If you use this method to create a statement, you are not limited to the SELECT statement as you are with the wizard.

  1. With the SQL Query node selected and connection information specified, move to the SQL tab and then click Import.

  2. On the next dialog box, select the file that contains the *.lpd or *.xml file that you want to import.

  3. You can make updates to the query, if needed, by specifying directly onto the Activity Info panel.

  4. When you are finished creating your statement, test it.

    • Use the Generate Statement button to create the statement.

    • Use the Execute Statement button to test the statement.

    • Use the Return Row Limit to set a limit on the number of rows that will be returned. You can use this option if you think your statement might fetch a large enough number of records to generate an overflow. Limit the output to small number of records so that you can verify that your statement is correct.

  5. When you are satisfied with your statement, click Finish. The SQL Query activity node, including the statement you just created, will become part of the process you are currently creating.

Note: 

Suppose you need to query multiple fields from multiple tables, and these fields are named the same. In this scenario, since the multiple fields have the same name, only one field value would be placed in the IPA variable.

For example, select emp.id, ins.id from Employee emp, Insurance ins where emp.id = ins.empid

IPA then creates only one variable named <NodeName>_id, so only one id value from the query will be available to the flow. To access both the field values in the flow, use one of these methods:

  • IPA creates one more variable for the field that includes the field position:

    <NodeName>_<Field Position as mentioned in the select query>

    In the given example, the following variables will be created:

    <NodeName>_1 and <NodeName>_2

    If these variables are used in the flow, then both the field values will be available to the flow.

    or

  • Write the select query with an alias name for the fields that are named the same. If done this way, then IPA variables will be created as per the alias name and not by the field name:

    Select emp.id as empid and ins.id as insid from Employee emp, Insurance ins where emp.id = ins.empid

    In the given example, IPA will create the following variables instead of using the actual field names:

    <NodeName>_empid and <NodeName>_insid

Importing an existing SQL statement

If you have created a process that contains a query similar to the one you want to create, you can import it to use as a template.

If you use this method to create a statement, you are not limited to the SELECT statement as you are with the wizard.

  1. With the SQL Query node selected and connection information specified, move to the SQL tab and then click Import.

  2. On the next dialog box, select the file that contains the *.lpd or *.xml file that you want to import.

  3. You can make updates to the query, if needed, by specifying directly onto the Activity Info panel.

  4. When you are finished creating your statement, test it.

    • Use the Generate Statement button to create the statement.

    • Use the Execute Statement button to test the statement.

    • Use the Return Row Limit to set a limit on the number of rows that will be returned. You can use this option if you think your statement might fetch a large enough number of records to generate an overflow. Limit the output to small number of records so that you can verify that your statement is correct.

  5. When you are satisfied with your statement, click Finish. The SQL Query activity node, including the statement you just created, will become part of the process you are currently creating.