Generating initial query code

Selecting tables

You select one table at a time. The selected table appears with a check mark next to it. 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. Here, you have the option to generate the query with 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. Here, you have the option to generate the query with a FOR UPDATE clause.
SELECT (Singleton): Generates a SELECT statement for the selected table using the name specified in the SP Name field.
SELECT DISTINCT (Singleton): Generates a SELECT DISTINCT statement for the selected table using the name specified in the SP Name field.
UPDATE: Generates an UPDATE statement for the selected table. Here, you have the option to generate the query with a WHERE CURRENT OF clause.
INSERT: Generates an INSERT statement for the selected table using the name specified in the SP Name field.
DELETE: Generates a DELETE statement for the selected table. Here, you have the option to generate the query with 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.