Select SQL Wizard

Basic settings screen

 

Numbering in the figure	Name	Description	Remarks
(1)	Table list view	Displays a list of tables.	The number of tables listed is determined in the settings for connection resources.
(2)	Table information pane	Displays the information on the selected table.	The table structure, table contents, and table relation information are displayed. 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 tab, refer to Table relation information.
(3)	Add Column/Table	Adds the column selected in the Table structure tab in the 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 all the columns to be read.
(4)	Extraction column editor	Set the writing order of the 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	Moves the selected column up.
(6)	Down	Moves the selected column down.
(7)	Remove	Deletes the selected column.
(8)	Generate SQL Statement	Automatically generates a SQL statement based on the settings.

Join conditions settings screen

 

Numbering in the figure	Name	Description	Remarks
(1)	Join condition list view	Displays a list of join conditions between read tables.	SQL statements are generated in the listed order.
(2)	Join condition editor	Configure join conditions between read tables.	The configured join conditions are displayed in the join condition list view.
(3)	Edit of join conditions	Add, edit and delete join conditions.	To edit a joint condition, select a join condition from the join condition list view and click Edit. To add a new join condition, click Add. To delete a join condition, select the one from the join condition list view and click Delete.
(4)	Join type	Configure a join type. 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 screen (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 listed order.
(2)	Up	Moves an extraction condition up.
(3)	Down	Moves 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 of extraction conditions	Add, edit and delete extraction conditions.	To edit an extraction condition, select it in the extraction condition list view and click Edit. To add a new extraction condition, click Add. To delete an extraction condition, select it in the extraction condition view and click Delete.
(6)	Table, Column	Set the names for the table and the column for which to specify extraction conditions.
(7)	Condition type	Configure the type 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 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	Specify conditions for a read column.	Depending on the data type of the read column, single quotes are automatically added to Value. For information on the data types to which automatic addition is applicable, refer to Automatic addition of single quotes. If (Enter directly) is selected for Condition type, for each database, use a consistent format in Value. 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 a logic operator for multiple conditions. AND OR

Condition and Sorting settings screen (Sorting)

 

Numbering in the figure	Name	Description	Remarks
(1)	Sort conditions list view	Displays a list of sort conditions for a read table.	SQL statements are generated in the listed order.
(2)	Up	Moves a sort condition up.
(3)	Down	Moves a sort condition down.
(4)	Sort condition editor	Configure sort conditions for a read table.	The sort conditions are displayed in the sort condition list view.
(5)	Edit of 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 the name for the table and the column for which to specify sort conditions.
(7)	Order	Configure the sort order of a read column. Ascending Descending

Generated SQL statement and execution test screen

 

Numbering in the figure	Name	Description	Remarks
(1)	SQL statement tab	Displays an automatically generated SQL statement.
(2)	Execution result tab	Displays the result of test execution.
(3)	Error tab	Displays errors if errors occur during test execution.
(4)	SQL statement editor	You can edit an automatically generated SQL statement. You can also write SQL functions which can't be generated in SQL Wizard.	Note If a SQL statement is edited manually, it can't be re-edited in the wizard.
(5)	Setting of formal parameters	If a script variable is used in an extraction condition, you can replace the script value with the value that is set in Value and execute a test of the SQL statement. This is enabled if Specify the temporary values to arguments for execution test is selected.	Note It isn't possible to specify a formal parameter to an environmental variable or a SQL parameter.
(6)	Execution test	Executes a SQL statement.	If a script variable is specified in the SQL statement, you can enter values for the variable and execute the statement. It is executed with empty characters if no value is entered. Entered values don't affect the script execution.

Join multiple tables

To join multiple tables, follow the steps below.

1. Click Add in the Joint condition settings screen.

2. In the join condition editor, specify the column for the join key in 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)	For both the right and left tables, records are retrieved only if the column values are identical.	The left table indicates a column specified in Target table/Target column. The right table indicates a column specified in Relation table/Relation column.
   LEFT OUTER JOIN (all records on the left and records with equal column value on the right)	For the left table, all records are retrieved. For the right table, records are retrieved only if the column values are identical.
   RIGHT OUTER JOIN (all records on the right and records with equal column value on the left)	For the right table, all records are retrieved. For the left table, records are retrieved only if the column values are identical.

   Example: The "Products" table and the "Sales" table are joined using "Product No." as a key.

    

   Products	Sales
   Product No. Product Name 1 Product A 2 Product B 4 Product D	Product No. Order amount 1 10 2 30 3 50

   • INNER JOIN (records with equal column value in both tables only)

     Results

     Product No.	Product Name	Order amount
     1	Product A	10
     2	Product B	30

    

   • LEFT OUTER JOIN (all records on the left and records with equal column value on the right)

     Results

     Product No.	Product Name	Order amount
     1	Product A	10
     2	Product B	30
     4	Product D

   • RIGHT OUTER JOIN (all records on the right and records with equal column value on the left)

     Results

     Product No.	Product Name	Order amount
     1	Product A	10
     2	Product B	30
     3		50

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 isn't added in the extraction column editor, you first need to add the table to the extraction column editor and configure join conditions settings. Then go back to the Basic settings screen to delete the table.

Example:

SELECT 
tableA.* 
FROM ( 
tableA 
INNER JOIN tableB ON tableA.primaryKey = tableB.primaryKey )

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 join condition between the "tableA" and the "tableB".

4. Click Back to jump 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. In the Condition and Sorting settings screen, select the Condition tab and click Add.

2. In the extraction condition editor, specify the column for the condition in 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 AND or OR from the AND/OR column list.

Setting 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 in Table/Column.

3. Select the sort order (Ascending or Descending) in Order.