SQL statement creation method

There are two methods for creating SQL statements: Simple and SQL statement.

(1) Simple specification

Simple specification creates an SQL statement internally and automatically to input data to and output data from the database, based on the Table name, Sort order, and Field name settings specified in the database table information.

a) Example of creating SQL statements for input

Assume that fields "col1" and "col2" are defined in the table "emp".

Figure 4.99 Example of creating SQL statements for input

b) Example of creating SQL statements for output

Assume that fields "col1" and "col2" are defined in a table "emp" and mapped to "Input data1" and "Input data2", respectively.

Figure 4.100 Example of creating SQL statements for output

c) Setting the update method

You can specify the method for updating a table in the Database Table - Detailed Information screen.

  • INSERT: Registers (INSERT) all records.

  • INSERT by using direct path loading function: Registers (INSERT) records using the Oracle direct path load function. This option is displayed only when the destination is an Oracle database.

  • BULK INSERT：: Registers records by using BULK INSERT. This option is displayed only when the destination is an ODBC database. You can use this option only when the destination is Redshift or Snowflake.

  • UPDATE rows that match the key value: Updates (UPDATE) records that match the key value, and registers (INSERT) records that do not match the key value. If no key value is specified, the registration operation (INSERT) is always conducted.

(2) SQL statement specification

You can describe SQL statements to input data to or output data from the database with no restrictions.

a) SQL statements for input settings

If I/O category is set to Input, specify a SELECT statement to obtain data from the database.

b) Describing input data in SQL statements for output settings

If you want to issue an SQL statement for output (INSERT or UPDATE) that includes input data specified in the mapping information, describe the statement as follows:

Table 4.8 Method for describing input data in an SQL statement

Database

Description method

Oracle

:xxx (#1)

DB2

?

SQLite3

?

SQL Server (#2)

?

PostgreSQL

$n (#3)

ODBC

(#4)

#1

:

Specify any character for xxx. Note, however, that you cannot use a question mark (?).

If you are going to describe multiple input data items in an SQL statement, you can use either the same character or different characters for each data item.

Example:

UPDATE emp SET col2=?, col3=? WHERE col1=?; (for DB2) UPDATE emp SET col2=:1, col3=:2 WHERE col1=:3; (for Oracle)

#2

:

For Windows only.

#3

:

Specify any numeric for n.

#4

:

For details about the description method of input data, see the documents of the database to be connected.

You must describe as many of the above input data items as there are enabled fields.

In the following case, there are three fields for which Status is "Enabled", so you must describe three input data items.

Screen 4.58 Case where three fields are "Enabled"

Note

  • For the correct execution of SQL statements for output, Concatenation sequence must be set as shown below. For Oracle, SQL statements are also processed according to Concatenation sequence, regardless of numbers in the SQL statements, such as ":1", ":2", and ":3".

  • In PostgreSQL, data items are processed in order of the numbers that are described in an SQL statement according to the description method.

    In PostgreSQL, "$1", "$2", "$3", and so on are assigned to fields for which Status is "Enabled" in order of Concatenation sequence.

    In the following case, "$1", "$2", and "$3" are assigned to "col1", "col5", and "col4" respectively.

     

    Screen 4.59 Concatenation sequence

    To avoid complex SQL statements, when you describe input data items such as "$1", "$2", and "$3" in PostgreSQL, it is recommended that you set Concatenation sequence in ascending order as follows, and describe the corresponding "$1", "$2", and "$3" in order of Concatenation sequence in an SQL statement.

    Screen 4.60 Displaying fields in ascending order of Concatenation sequence

    • Example of SQL statement）

    insert into "test" values($1, NULL, NULL, $2, $3)

  • If you specify a SELECT statement for SQL1 or SQL2, an error might occur.

c) Execution method of SQL statement for output

When outputting databases, you can execute two different SQL statements.

For example, if you want to execute an INSERT statement first, and then issue an UPDATE statement only when an error occurred due to a unique constraint of PRIMARY KEY, you can specify the SQL statements as described below.

Figure 4.101 Example of execution of SQL1 and SQL2 (for Oracle)

Note

Even if Execute SQL 2 only if SQL 1 fails is specified, SQL 2 might not be executed; for example, when the syntax of the specified statement is incorrect.