To define relational data sources, select the Relational Data Source radio button on the Select Server Data Source panel and click [Next].
Note: When Relational Data Source is selected, Analyzer displays check boxes to enable you to display a list of registered data sources (the default) and/or ODBC drivers installed on the chosen Windows Server.
The Select Data Source or Subsystem panel is displayed. To define relational tables, you must specify the data source or database subsystem. A list of data sources and subsystems that were defined for the Arbutus Windows Server will be displayed.
The list of data sources can be restricted or expanded by selecting or deselecting the check boxes for Data Sources, Drivers and File DSN's. Schema's can also be selected.
Check the "Save As Default" check box to save the selected data source as the default for future usage.
Check the "Convert Imported Unicode Data to ASCII" check box if you want any SQL-based Unicode data that is read to be converted to ASCII. This option should only be used if you are confident that the source data only contains North American English characters.
Note: The ASCII keyword is automatically inserted into the Import ODBC/OPENEXCEL command syntax generated by the Data Definition Wizard if SET SQLASCII is turned on, if the "Convert Imported Unicode Data to ASCII" table preference is selected, or if the "Convert Imported Unicode Data to ASCII" check box is selected in the Data Definition Wizard via either the Select Data Source or Subsystem panel or the File Format panel.
For more information see Convert Imported Unicode Data to ASCII or Set SQLASCII.
If your company uses password protection, enter your user name and password. If you need help with your user name and password, contact your database or systems administrator. If the Save check box is selected the user name and password are encrypted and stored in the users local registry. If you need help with your user name and password, contact your database or systems administrator.
Select the desired table(s) from the desired schema. Click [Next].
Note: Depending on the chosen data source, the Select Tables dialog may display check boxes to allow selection of Tables, Views or Aliases.
To select columns and to edit the Where and Order clauses, de-select Define Multiple Tables, select Edit Query and click [Next]. Otherwise, clicking [Next] will advance directly to the Final panel.
To create a custom SQL Query, check the Create SELECT check box and click [Next]. Enter/paste a valid SQL Select statement in the SQL SELECT dialog that appears and then click [OK] to advance to the Final panel.
If more than one table or worksheet/named range is selected, and Define Multiple Tables is de-selected, you will need to specify the key fields to be used to join the tables/worksheets. Click [OK].
The Select Tables dialog now displays the specified join criteria. Click [Next].
To define multiple tables/worksheets independently at one time, select Define Multiple Tables.
After selecting the desired tables/worksheets, clicking [Next] will advance you directly to the Final panel.
Each table layout will be named based on the table name and the selected tables can either be flattened or read directly.
By default, the Relational Data Source Wizard selects all available columns. If you checked the “Edit Query” check box on the Select Table panel, you can choose only desired columns in the order you want them. Click the left arrow button, the right arrow button, [Clear All] or [Add All] to change the selection of columns you want to import.
For individual character and numeric fields in a relational data source, the Select Columns dialog provides the dynamic ability to override the maximum string length or maximum decimals. This is particularly useful for large memo fields or to ensure consistency with decimal precision.
Use the Table Data pull-down menu to select columns from each table if more than one table was selected. Click [Next] to proceed.
You can specify SQL Select or Where and Order clauses to select or filter and order the data.
Note: You will only be prompted to edit the SQL Select statement or specify Where and Order clause if the “Edit Query” check box is selected on the Select Tables panel. Otherwise this panel is bypassed.
Note: To subsequently view or edit the SQL Select or Condition, Where and Order clauses, open the table layout, click Edit and select Table Layout and then choose the Table Options tab. See Table Options
Note: To subsequently override the SQL Select or Condition, Where and Order clauses using system variables on the command line or procedures see Overriding the SQL Select Statement and Modifying the Database Condition, Where and Order Clauses.
The underlying SQL Select statement can edited in the Wizard by checking the Edit SQL Select check box on the Where and Order clause panel in the Wizard. clicking [Next] allows you to edit the underlying SQL Select statement for the selected table(s).
Note: Edit the SQL Select statement with extreme caution!
Analyzer supports standard ANSI SQL. The Select statement is permanently stored within the table layout (see notes below). Click Validate to verify the edited SQL Select clause.
It is useful to ensure that all required fields were selected from all tables prior to editing the SQL Select statement. If not, simply click [Back] to get to the Select Columns dialog and then select all necessary fields.
In the Where and Order clause panel in the Wizard, you can specify the relational database Where and Order clause information using standard ANSI SQL. This information is then permanently stored in the table layout.
Note: To add the full column name to the Where or Order clause, place the cursor in the desired clause text box and double click on the desired column name.
Note: The DB Where and Order variables should only reference native key fields, and all DB variables should be used on live data sources only. For more information on native key fields, see Native and Key Fields
There are several options in this panel:
| • | In the Where Clause text box, enter a Structured Query Language (SQL) “Where” statement if you want to limit the records imported. For example: Amount>0. This SQL Where Clause only applies to the selected table. |
Note: This is not an Analyzer “If” test. The SQL “Where” statement is processed by the ODBC driver and must be valid for this usage.
| • | In the Order Clause text box, enter a Structured Query Language (SQL) “Order” statement if you want to order the records imported. This SQL Order Clause only applies to the selected table. |
| • | To check the validity of the entered Where and Order clause, click the “Validate” button. Errors are reported in a text box. |
Click [Next]. The Wizard proceeds to the Final panel. See Final Panel.