Modify the SQL Statement

Home | FAQs & Troubleshooting

 

Expand/Collapse all Show/Hide All

Expand/Collapse item  Overview

The Visual Integrator module is designed to allow a user with no SQL training to easily retrieve information from an ODBC data source. By selecting the tables, data fields, and the linkage information, the Visual Integrator module creates a SQL statement automatically to retrieve the selected data from the data source. Because of its simplicity, there are many features and capabilities of SQL of which the standard default selection cannot take advantage.

To modify the SQL statement, you must have a thorough understanding of the SQL syntax and how your database product works. With a good working knowledge of SQL, you can take advantage of features not supported by the default selection logic. This task is available only with the appropriate security setup.

Expand/Collapse item  Before You Begin

The SQL statement should be modified only under the direction of a qualified Sage support representative.

 

To modify the SQL statement

  1. Select Visual Integrator Main menu > Import Job Maintenance.

  2. In the Import Job Selection window, enter a job name and table name, and then click Accept. For more information, see Import Job Selection - Fields.

  3. In Import Job Maintenance, in the File Type field, select ODBC Data Source. For more information, see Import Job Maintenance - Fields.

  4. In the Data Source field, select an ODBC data source. Click Tables.

  5. In the ODBC Table Selection window, click the SQL Statement tab. For more information, see ODBC Table Selection - Fields.

  6. Click Modify to enter the actual SQL statement to use to retrieve the data from the data source.

  7. You can modify the default SQL statement to take advantage of the following features.

  8. Use left outer joins to create efficient selection logic in order to retrieve data from multiple tables.

  9. Use a query or view that has been previously created in the database.

  10. Change the order in which records are retrieved from the tables. The default SQL statement orders the retrieved data by the first field of the primary (first) table.

  11. Create calculated fields to be imported.

  1. After you have modified the SQL statement, you cannot access the other tabs in the ODBC Table Selection window because the customized SQL statement overrides the information on those tabs. To unlock these tabs and reset the SQL statement to the default value, click Reset.

  2. If you have modified the SQL statement, click Test to verify that you are retrieving the data properly.

The SQL statement is updated.