Select SQL Wizard

SQL Wizard

SQL statements can be easily created with the Wizard .

Start SQL Wizard (Specifying SQL Statement)

To start the SQL wizard, enter [SQL wizard] in the properties settings dialog in Execute SQL Wizard (Specifying SQL Statement) in the database adapter.

Screen elements of SQL Wizard (Specifying SQL Statement)

Basic settings screen



Numbering in the figure Name Description Remarks
(1) Table list view Display a list of tables.
  • The maximum number of tables listed is determined by Global Resource settings.
  • To add a table, select [Add] in the right-click menu in the root element and specify the table name.
(2) Table Canvas Configure the extraction definition of a read table.
  • To delete a read table, select [Remove] in the right-click menu on the table.
(3) Extracted Column Editor Configure the order to write a read column and its alias.
  • An alias is used for describing a result of a SQL statement and a Mapper schema.
(4) Root Elements Display the database name of the connecting destination.
  • Tables can be added by selecting [Add] in the right-click menu of the root element.
(5) Read Table Tables to be read.
Specify which column to read.
  • The columns checked will be added to the extracted column editor.
  • If you check the checkbox located on the left of [* (All columns)], you can set all columns to be read-only.
(6) Mapping Link (Join) Define the key column and joint conditions.  
(7) Up Move a read column up.  
(8) Down Move a read column down.  
(9) Generate SQL statement Automatically generate a SQL statement based on the settings.  
(10) Help Start Help and display the SQL Wizard (Specifying SQL Statement) page.  

Detailed Settings Screen (Conditions)



Numbering in the figure Name Description Remarks
(1) Table Canvas Configure the definition of read table definition.  
(2) Condition Column Editor Configure the condition of read columns.
  • Please use the consistent format for each database.
    Example:
    • If you want to read the record in which the value of the condition column is "Product A", set as "='Product A'"
    • If you want to read the record in which the value of the condition column is "Product A" or "Product B", set as "IN('Product A', 'Product B'".
(3) Add to condition Select columns you from the read table and add to the condition column.  
(4) and/or Logic operators for multiple conditions.
  • [AND]
  • [OR]
 
(5) Up Move a read column up.  
(6) Down Move a read column down.  
(7) Remove Delete a read column from a condition column editor.  

Detailed Settings Screen (Sort Order)



Numbering in the figure Name Description Remarks
(1) Extracted Column Editor Columns for read-only.  
(2) Sorting Condition Editor Configure the order of defining and sorting columns.  
(3) Move columns Move extracted columns and sorting conditions.
  • [>]:Add only the selected columns to the sorting conditions
  • [>>]:Add all columns to the sorting conditions
  • [<]:Exclude only the selected columns from the sorting conditions
  • [<<]:Exclude all columns from the sorting conditions
 
(4) Order of sorting Configures the order of sorting read columns.
  • Ascending
  • Descending
 
(5) Up Move a read column up.  
(6) Down Move a read column down.  

The contents of a SQL Statement and the Test Execution Screen



Numbering in the figure Name Description Remarks
(1) SQL statement tab Display an automatically generated SQL Statement.  
(2) Execution result tab Display the results of the test execution.  
(3) Error tab If an error occurs during the test execution, the error is displayed in the error tab.  
(4) SQL Statement Editor It is possible to edit the automatically generated SQL Statement.
Also possible to write SQL functions which cannot be generated in the SQL Wizard.
  • If a SQL Statement is manually edited, it is not possible to edit in the SQL Wizard.
(5) Execution test Execute a SQL Statement.  

Creating a SQL Statement

To create a SQL Statement, follow the below steps.
  1. From the table list view, drag & drop the table you want to read onto the table canvas.

  2. Select a column to read from the check box in the read table.

  3. To specify the order of defining read columns and an alias.

  4. Click [Generate SQL statement] to generate a SQL statement.
    To combine multiple tables, refer to Join multiple tables.
    To specify read conditions, click [Next] and refer to Specify extracting conditions or Set sort order.

  5. A SQL statement is generated in the SQL statement editor in the [SQL statement] tab.
    Click [Submit] to save the SQL statement and end the SQL wizard.

  6. To execute the SQL statement, click [Execution test].

  7. The results are shown in the [Execution result] tab.

  8. If an error occurs in the test execution, detail is shown in the [Error] tab.

  9. Click [Submit] to save the SQL statement and end the SQL wizard.

  10. To edit the SQL statement, click [SQL wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement).
    If the SQL Statement is edited manually, not possible to edit in the Wizard.

Join multiple tables

To join multiple tables, follow the below steps.
  1. From the table list view, drag & drop the tables you want to join to the table canvas and select the columns to read.

  2. Create a mapping of columns to be set as keys by drag & drop.

  3. Select [Properties] in the right-click menu in the Mapping link.

  4. Select joint conditions in the "Join properties" screen.
    Description of the Items
    Item name Description Remarks
    Fetch row only if column values of both tables are identical (default). Fetch records only if column values of both tables are identical.
    • The mapping link is displayed as a straight line.
    Fetch all rows from `<the first table>`, even if `<the second table>` does not contain a matching row. The first table fetches all records, and the second table fetches records only if column values of both tables are identical.
    • A mapping link is displayed as an arrow from the first table to the second.
    Fetch all rows from `<the second table>`, even if `<the first table>` does not contain a matching row. The second table fetches all records, and the first table fetches records only if column values of both tables are identical.
    • A mapping link is displayed as an arrow from the second table to the first.

    Example) The following shows joining "Products" table and "Sales" table using "Product No." as key.

    Products Sales
    Product No.Product Name
    1Product A
    2Product B
    4Product D
    Product No.Order amount
    110
    230
    350

Set Extraction Conditions

To set extraction conditions, follow the below steps.
  1. Select a column to use as a condition in the read table, and click [Add to condition].

  2. Set [value] of the condition column in the extraction condition editor.

  3. If conditions for multiple columns are required, set logic operators (AND/OR).
    Select from [AND][OR] from the [and/or] column list.

Set sort order

To set the sort order, follow the below steps.
  1. Select the tab of the [Sorting] and add column to extract to sort criteria.

  2. Select the column to add to sort criteria, and add with [>].

  3. [Order of sorting] of Sort criteria is specified with [Ascending] or [Descending].

Specification Limits

Main exceptions

None