Generating initial query code

The OpenESQL Assistant generates the initial query code after you have selected a table and a query type.

Selecting tables

You select one table at a time. The selected table appears with a check mark next to the table icon, Checked Table . Once your initial SQL query code has been generated, you can select additional tables.

Note: Some databases use special characters in the names of system tables. For example, Oracle can use system tables whose names contain a dollar sign ($). When the OpenESQL Assistant encounters a special character in the name of system table that would render the generated table name illegal, it automatically encloses the column and table names in quotes.

Selecting a query type

Restriction: You can only generate TSQL stored procedures if you are connected to SQL Server data source.

Once you have selected a table, the Select Type of Query to Create dialog box appears. This dialog box contains a list of query types you can perform on your selection. Depending on the type of query you select, you have some options. You can create the following types of queries including these options:

SELECT (cursor)

Generates a SELECT statement for the selected table using the cursor specified in the Cursor Name field and including a table alias. You have the following options, which are mutually exclusive:

Generate the query as a TSQL or SQL CLR stored procedure with the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.
Generate an SQL query containing a FOR UPDATE clause.

SELECT DISTINCT (cursor)

Generates a SELECT DISTINCT statement for the selected table using the cursor specified in the Cursor Name field. You have the following options, which are mutually exclusive:

Generate the query as a TSQL or SQL CLR stored procedure with the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.
Generate an SQL query containing a FOR UPDATE clause.

SELECT (Singleton)

Generates a SELECT statement for the selected table. You have the option to generate the query as a TSQL or SQL CLR stored procedure using the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.

SELECT DISTINCT (Singleton)

Generates a SELECT DISTINCT statement for the selected table. You have the option to generate the query as a TSQL or SQL CLR stored procedure using the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.

UPDATE

Generates an UPDATE statement for the selected table. You have the following options, which are mutually exclusive:

Generate the query as a TSQL or SQL CLR stored procedure with the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.
Generate an SQL query containing a WHERE CURRENT OF clause.

INSERT

Generates an INSERT statement for the selected table. You have the option to generate the query as a TSQL or SQL CLR stored procedure using the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.

DELETE

Generates a DELETE statement for the selected table. You have the following options, which are mutually exclusive:

Generate the query as a TSQL or SQL CLR stored procedure with the name specified in the SP Name field. The type of stored procedure generated depends on the setting of the Type Stored Procedure option.
Generate an SQL query containing a WHERE CURRENT OF clause.

Initial query generation

Once you have selected a query type and options, the initial SQL code for the query you have selected is automatically generated and displayed underneath the Query tab. At the same time, a list of all the columns in the table is displayed underneath the table name.

For SELECT queries, a table alias is automatically generated by the OpenESQL Assistant. For example:

SELECT FROM Customer A

The first table selected generates an alias of the letter A. If you select a second table, the OpenESQL Assistant generates an alias of B and so on.

You can use the query as is, or continue to build the query by adding tables, columns, search criteria, and auxiliary code as necessary.