Select SQL Wizard

SQL Wizard

SQL statements can be easily created with the Wizard .

Start a SQL Wizard

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

Screen elements of SQL Wizard (Specifying SQL Statement)

Basic settings



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.
(2) Table information pane Displays the information on the selected table.
  • Displays table structures, table data, and table related information
  • For information displayed in the [Table structure] tab, refer to Table structure.
  • For information displayed in the [Table contents] tab, refer to Table contents.
  • For information displayed in the [Table relation information], refer to Table relation information.
(3) Add Column/Table Add the column selected in the [Table structure] tab in Table Information Pane to the extraction column editor.
  • By selecting the table in the table list view and clicking [Add Column/Table], you can set the all columns read-only.
(4) Extracted column editor Set the order of writing read columns and an alias (the name of the extraction result column).
  • An alias is used for describing the result of a SQL statement and a Mapper schema.
(5) Up Move a read column up.  
(6) Down Move a read column down.  
(7) Remove Delete a read column.  
(8) Generate SQL Statement Automatically generate a SQL statement based on the settings.  

Join condition settings



Numbering in the figure Name Description Remarks
(1) Join condition list view Display a list of join conditions between read tables.
  • SQL statements are generated in the display order.
(2) Join condition editor Configure join conditions between read tables.
  • The join conditions are displayed in the join condition list view.
(3) Edit join conditions Add, edit and delete join conditions.
  • To edit joint conditions, select joint conditions from the joint condition list view and click [Edit].
  • To add a new joint condition, click [Add].
  • To delete a join condition, select the one from the join condition list view and click [Delete].
(4) Join type Configure join types.
  • [INNER JOIN (records with equal column value in both tables only)]
  • [LEFT OUTER JOIN (all records on the left and records with equal column value on the right)]
  • [RIGHT OUTER JOIN (all records on the right and records with equal column value on the left)]
  
(5) Target table, Target column Set a name for the table and the column to be joined.  
(6) Relation table, Relation column Set a name for the table and the column to be joined.  

Condition and Sorting settings(Condition)



Numbering in the figure Name Description Remarks
(1) Condition and Sorting settings Displays a list of extraction conditions for a read table.
  • SQL statements are generated in the order of list.
(2) Up Move an extraction condition up.  
(3) Down Move an extraction condition down.  
(4) Extraction Condition Editor Configure extraction conditions for a read table.
  • The extraction conditions are displayed in the extraction condition list view.
(5) Edit extraction conditions Add, edit and delete extraction conditions.
  • To edit an extraction condition, select the one in the extraction condition list view and click [Edit].
  • To add a new extraction condition, click [Add].
  • To delete an extraction condition, select the one in the extraction condition view and click [Delete].
(6) Table, Column Set a name for the table and the column to specify extraction conditions.  
(7) Condition type Configures the types of extraction conditions.
  • [(Enter directly)]
  • [Equal to]
  • [Not equal to]
  • [Greater than]
  • [Less than]
  • [Greater than or equal to]
  • [Less than or equal to]
  • If [(Enter directly)] is selected, the contents entered in the [Value] are directly added in a SQL statement.
  • If [Equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] =[Value]" format.
  • If [Not equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] !=[Value]" format.
  • If [Greater than] is selected, conditions are added to the SQL statement in the "[Table].[Column] >[Value]" format.
  • If [Less than] is selected, conditions are added to the SQL statement in the "[Table].[Column] <[Value]" format.
  • If [Greater than or equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] <=[Value]" format.
  • If [Less than or equal to] is selected, conditions are added to the SQL statement in the "[Table].[Column] >=[Value]" format.
(8) Value Specifies the read column conditions.
  • Depending on the data type of the read column, single quotes are automatically added to [Value].
    For information on the data types applicable to automatic addition, refer to "Automatic addition of Single Quotes".
  • If [(Enter directly)] is selected in [Condition type], please use the consistent format for each database.
    Example:
    • If you want to read the records in which the value of the condition column is "Product A", set as "='Product A'"
    • If you want to read the records in which the value of the condition column is "Product A" or "Product B", set as "IN('Product A', 'Product B'".
(9) AND/OR Specify logic operators for multiple conditions.
  • [AND]
  • [OR]
  

Condition and Sorting settings(Sorting)



Numbering in the figure Name Description Remarks
(1) Sort conditions list view Display a list of sort conditions for a read table.
  • SQL statements are generated in the order of display.
(2) Up Move a sort condition up.  
(3) Down Move a sort condition down.  
(4) Sort Condition Editor Configure sort conditions for a read table.
  • Sort conditions are displayed in the sort condition list view.
(5) Edit sort conditions Add, edit and delete sort conditions.
  • To edit a sort condition, select the one you want to edit and click [Edit].
  • To add a new sort condition, click [Add].
  • To delete a sort condition, select the one you want to delete and click [Delete].
(6) Table, Column Set a name for the table and the column you specify sort conditions.  
(7) Order Configure the sort order of a read column.
  • [Ascending]
  • [Descending]
  

Generate SQL Statement and execution test



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 test execution.  
(3) Error tab If an error occurs during test execution, the error is displayed in this tab.  
(4) SQL statement editor You can edit an automatically generated SQL Statement.
Also possible to write SQL functions which cannot be generated in a SQL Wizard.
  • If a SQL Statement is edited manually, it is not possible to edit in the Wizard.
(5) Set a formal parameter If a script variable is used in the extraction condition, you can replace the script value to the set [Value] and execute a test to the SQL statement.
Enabled if [Specify the temporary values to arguments for execution test] is checked.
  • It is not possible to specify a formal parameter to an environmental variable or a SQL parameter.
(6) Execution Test Execute a SQL Statement.  

Generate a SQL statement

To generate a SQL Statement, follow the below steps.
  1. Select the table you want to read from the table list view and click [Add Column/Table]. (To specify a read column, select the column in the table information pane.)

  2. To specify the order of defining the read columns and an alias, configure with the extraction column editor.

  3. To generate a SQL statement with the below settings, click [Generate SQL Statement].
    To join multiple tables, refer to "Join multiple tables".
    If you want to specify a table not in a read table but in the joint condition, refer to "How to join tables not specified in the read table".
    To specify read conditions, click [Next] and refer to "Specify extraction conditions" and "Specify sort conditions".

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

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

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

  7. If an error occurs during test execution, the error is displayed in the [Error] tab.

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

  9. To edit a SQL statement, click [SQL Wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement) to start a SQL wizard.
    When SQL statement created by the SQL wizard has been manually edited, it cannot be re-edited by the wizard.

Join multiple tables

To join multiple tables, follow the below steps.
  1. Click [Add] in the "Joint condition settings" screen.

  2. In the joint condition editor, specify the column for the joint key as [Target table][Target column] as well as [Relation table][Relation column].

  3. Select [Join type].
    Item Description
    Item name Description Remarks
    [INNER JOIN (records with equal column value in both tables only)] Fetch records only if the column values in the both tables are identical.
    • The table on the left indicates a column specified as [Target table][Target column].
    • The table on the right indicates a column specified as [Relation table][Relation column].
    [LEFT OUTER JOIN (all records on the left and records with equal column value on the right)] The left tables set all records as results. The right tables set records in which the column values are identical as results.  
    [RIGHT OUTER JOIN (all records on the right and records with equal column value on the left)] The right tables set all records as results. The left tables set records in which the column values are identical as results.  

    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

How to join tables not specified in a read table

For [Target table][Relation table], the tables added to the extraction column editor in the "Basic settings" screen are displayed.
In a case you want to specify a table which is not added in the extraction column editor, you first need to add the table to the extraction column editor and configure joint conditions settings. Then go back to the "Basic settings" screen to delete the table.
To generate a SQL like above, follow the steps below.
  1. In the "Basic settings" screen, add "tableA" and "tableB" to the extraction column editor.

  2. Click [Next] to jump to the "Join condition settings" screen.

  3. Create a joint condition between the "tableA" and the "tableB".

  4. Click [Back] to go back to the "Basic settings" screen, and delete "tableB" from the extraction column editor.

  5. Click [Generate SQL Statement].

Extraction condition settings

To configure extraction conditions, follow the steps below.
  1. Select the "Condition and Sorting settings" screen and click [Add].

  2. In the extraction condition editor, specify the column for the condition as [Table][Column].

  3. In the extraction condition editor, specify [Condition type] and [Value].

  4. If conditions for multiple columns are required, set logic operators (AND/OR).
    Select from [AND][OR] from the conditional connection list.

Set the sort order

To set the order for sorting, follow the steps below.
  1. In the "Condition and Sorting settings" screen, select the [Sorting] tab and click [Add].

  2. In the sort condition editor, specify the column for the condition as [Table][Column].

  3. Select the sort order from the [Order] list; [Ascending] and [Descending].

Automatic Addition of Single Quotes

If the column data type falls into one of the below, single quotes are automatically added to the [Value] set is the extraction condition editor.

Specification Limits

Main exceptions

None