Using Query Editor

Creating New Queries

Centerprise Data Integrator provides the ability to create and display multiple queries while connected to different database servers, using the same user interface. The queries written are specific for each type of database. For your reference, a link to the query syntax documentation for each database server is given below.

Supported Database	Query Syntax
MS SQL Server	http://msdn2.microsoft.com/en-us/library/aa372021.aspx
IBM DB2	http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp
Oracle	http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements.htm#SQLRF002
Sybase	http://infocenter.sybase.com/help/index.jsp
MS Access	http://office.microsoft.com/en-us/access/HA101315811033.aspx
My SQL	http://dev.mysql.com/doc/refman/6.0/en/index.html

When a query starts it is not connected to the database. Hence, the New Connection Information dialog box is also launched at the same time. You can follow the steps for connecting to a database, for establishing a query connection as the process is the same.

Steps:

1. Click File>New>Query.

OR

Press Ctrl+N. A new query is started and the New Connection Information dialog box appears. The Query module appears on the Main Menu.

Note: You can also click on the toolbar and select Query from the dropdown

2. Follow steps 1 to 11 for Connecting to Single Database to establish a query connection.

You are ready to write query scripts in the Query Pane. You can write a new query or use the existing SQL Snippets to build your query. The Data Source Browser provides additional functionality while working with queries. For more information, click Using Data Source Browser with Queries. You can view the SQL Snippets window and the Data Source Browser from the Menu bar. Both these windows can be set to Auto-hide such that they get attached to one side of the application screen to maximize the Query Pane. The window reappears when you mouse over the tab.

Use the command buttons on the File Toolbar and the Query Toolbar to save, run, modify or search the queries.

Working with SQL Snippets

Centerprise Data Integrator offers built-in code snippets called SQL Snippets to help you build your query. You can add these snippets to modify a query or build a new query.

Steps:

1. Click View>SQL Snippets. The SQL Snippets window appears.

2. In the SQL Snippets window, double-click a code snippet to add it to the active Query Pane. The code snippet is displayed in the Query Pane.

You can modify the existing template to write your own query.

Note: You can also press the shortcut Ctrl+K to add a snippet.

The query results can be exported and saved in a Microsoft Excel spreadsheet or a delimited file using the Export Query Results to Excel and Export Query Results to Delimited File respectively.

To export query results you can:

• Use the commands available in the Query menu on the Menu bar.

OR

• Click the Export Query buttons available on the Query Toolbar.

o Click the Export Query Results to Excel button to export the results of the query to a Microsoft Excel Spreadsheet.

o Click the Export Query Results to Delimited Filebutton to export the results of the query to a Delimited file.

OR

• Right-click in the Query Pane and select the desired command.