Understanding Code Assist

When SQL Editor has finished analyzing a partial piece of code, it displays a list of data source objects for you to select from.

SQL Editor takes the following into consideration when analyzing code for a list of possible data source objects for insertion:

● Text to be inserted

● Original text to be replaced

● Content assist request location in original text

● The database object represented by the insertion text

Generally, insertion suggestions use the following format:

<insertion_text > - <qualification_information >

Code assist is available for SELECT, UPDATE, INSERT, and DELETE statements, as well as stored procedures, and functions (built-in and user defined.)

Additionally, code suggestions can be made for DML statements nestled within DDL statements. This functions in the same manner as code assist for statements that are not nestled, and applies to CREATE PROCEDURE, FUNCTION, TRIGGER, TABLE, and VIEW statements.

When the code assist window is open, you can filter out singular object suggestions by pressing (Ctrl + Spacebar). This removes all objects from the assist window while retaining procedures and functions. To display objects again, press (Ctrl + Spacebar) again.

The following table displays a list of all possible object suggestions, and the format in which SQL Editor inserts the suggestions into a statement:

Object and Stored Procedure Suggestions

Object Suggestion	Syntax/Example
Table	(TABLE) [catalog].[schema] EMPLOYEE - (TABLE)HR
Alias Table	(TABLE ALIAS) [catalog].[schema]tableName EMPLOYEE-(TABLE ALIAS)HRJOBS
Column	datatype - (Column) [catalog].[schema].tableName JOB_TITLE: varchar(20)-(Column)HRJOBS
Alias Column	datatype - (COLUMN ALIAS) [catalog].[schema].tableName. columnName JOB_TITLE:int-(COLUMN ALIAS)HR.JOBS.JOB_ID
Schema	(SCHEMA) [catalog] dbo-(SCHEMA)NorthWind
Catalog	(CATALOG)
Call	Call HR.ADD_JOB_HISTORY

Function Suggestions

Function Suggestion	Syntax/Example
Built-in	SELECT A FROM HR.DEPARTMENTS WHERE HR.DEPARTMENTS AVG
User-Defined	SELECT + FROM HR.CLIENTS WHERE HR.F_PERSONAL

Note: Function suggestions are only available for Oracle and DB2 platforms.

SQL Editor detects incomplete or erroneous code, processes the code fragments, and then attempts to apply the appropriate logic to populate the code.

As code is typed into SQL Editor, the application ‘reads’ the language and returns suggestions based on full or partial syntax input.

Depending on the exact nature of the code, the automatic object suggestion feature behaves differently; this enables SQL Editor to provide reasonable and ‘intelligent’ suggestions on coding.

Additionally, semantic validations can be made for DML statements nestled within DDL statements. This functions in the same manner as validation for top-level statements, and applies to CREATE PROCEDURE, FUNCTION, TRIGGER, TABLE, and VIEW statements.

The following chart displays the possible statement fragments that SQL Editor will attempt to suggest/populate with objects:

Statement Fragment Elements

Object Suggestion Behavior

SELECT

A list of tables, when selected automatically, prompts the user to select a column.

UPDATE and DELETE

A list of tables appears in the FROM and/or WHERE clause.

INSERT

A list of tables and views appears in the INSERT INTO and OPEN BRACKET clause prior to values.

A list of columns based on the table or view name appears in the OPEN BRACKET or VALUES clause.

In addition to DML statements, SQL Editor also suggests objects based on specific fragmented syntax per line of code:

Statement Syntax

Object Suggestion Behavior

A partial DML statement (for example SEL ... indicates a fragment of the SELECT clause)

The keyword is completed automatically, assuming SQL Editor can match it. Otherwise, a list of suggested keywords is displayed.

If the preceding character is a period, and the word prior is a table or view, a list of columns appears.

If the word being typed is a part of a table name (denoted by a schema in front of it) the table name is autocompleted.

If the word being typed has a part of a column name (denoted by a table in front of it) the column name is autocompleted.

Without typing anything.

A list of keywords appears.

A period is typed.

If the word prior to the period is a name of a table or view, a list of columns is displayed.

If the word prior to the period is a schema name, a list of table names is displayed.

If the word prior to the period is either a table name or a schema name, then both a list of columns and a list of table names is displayed.

To activate code suggestions:

By default, code suggestions are automatically offered if you stop typing in SQL Editor for one second. You can turn off the automated suggestion feature on the Code Assist preferences page.

If automated code suggestion is disabled, you can still access the suggestion window using the following method:

1. Click the line that you want SQL Editor to suggest an object for.

2. Press (CTRL + Spacebar) on your keyboard. SQL Editor ‘reads’ the line and presents a list of tables, views or columns as appropriate based on statement elements.

Note: On a per platform basis, auto-suggestion behavior may vary. (For example, the WITH statement on DB2 platforms.)

To modify object suggestion parameters, including setting it from automatic to manual, see Specify Code Assist Preferences on page 16.

You can speed up the performance of the code assist functionality by enabling data source indexing either when you connect to the data source, see Working with Data Sources on page 45 or on the Preferences page, see Specify Data Source Indexing Preferences on page 27.