Select SQL Wizard

SQL Wizard

SQL statements can be easily created with the Wizard .

For DataSpider Studio for Web, see here.

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
(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.	If script variable or SQL parameter is specified in SQL statement, execution is enabled by entering values to them. Executed with empty character, if no value is entered. Entered value does not affect to the script execution.

Creating a SQL Statement

To create a SQL Statement, follow the below steps.

From the table list view, drag & drop the table you want to read onto the table canvas.

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

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

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.

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.

To execute the SQL statement, click [Execution test].
If script variable or SQL parameter is specified in SQL statement, execution is enabled by entering values to them.
- Executed with empty character, if no value is entered.
- Entered value does not affect to the script execution.

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

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

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

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. Select whether to edit the SQL statement before modification, or to create a new SQL statement in a dialog.

Join multiple tables

To join multiple tables, follow the below steps.

From the table list view, drag & drop the tables you want to join to the table canvas and select the columns to read.

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

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

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
1	Product A
2	Product B
4	Product D

Product No.	Order amount
1	10
2	30
3	50

When [Fetch row only if column values of both tables are identical (default).]
Results:

Product No. Product Name Order amount

1 Product A 10

2 Product B 30

When [Fetch all rows from `Products`, even if `Sales` does not contain a matching row.]
Results:

Product No. Product Name Order amount

1 Product A 10

2 Product B 30

4 Product D

When [Fetch all rows from `Sales`, even if `Products` does not contain a matching row.]
Results:

Product No. Product Name Order amount

1 Product A 10

2 Product B 30

3 50

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

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

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

Set Extraction Conditions

To set extraction conditions, follow the below steps.

Select a column to use as a condition in the read table, and click [Add to condition].

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

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.

Select the tab of the [Sorting] and add column to extract to sort criteria.

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

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

Specification Limits

Database table of which the schema name or table name contains "." cannot be dragged and dropped to Select table panel.

Test execution may be failed if the data amount is massive. In that case, please modify the heap size in DataSpiderServer.
For information on heap size settings, refer to "Property Reference".

The maximum record count shown in the [Execution result] tab is 100.

To join multiple tables, you need to make a mapping in the order you drag & drop from the table list view to the table canvas.

SQL statements cannot be edited if the tables in the SQL statement do not exist.

Main exceptions

None