## SQL Expression Editor

The SQL Expression Editor is added to the Field Explorer when you create a report based ODBC data source. The Editor also appears when you choose the SQL Expression Field command from the Insert menu.

Use the SQL Expression Editor to create and edit SQL expressions that can be used in your reports. You can use:

- `fields:
  `({customer.CUSTOMER LAST NAME}, {customer.LAST YEAR'S SALES}, and so on)
- numbers:
  (1, 2, 3.1416, and so on)
- text:
  ("Quantity", " ", ":", 'your text', and so on)
- operators:
  (+, -, and so on)
- functions:
  (TrimRight (x), Length (x), and so on).

The SQL Expression Editor is used to combine these components into SQL expressions. The SQL Expression Editor allows you to type the components into the SQL expression directly, or, in the case of fields, functions, operators, group field values, and formulas, to select them from lists of those items that are available.

The SQL Expression Editor requires you to enter the various components according to a specific set of rules called syntax. Some examples of rules:

- text enclosed in quotation marks
- arguments enclosed in parentheses (where applicable)
- referenced formulas identified with a leading @ sign, and so on.

The SQL Expression Editor checks the syntax and helps you debug (fix) problems before you enter the SQL expression field into the report.

##### SQL Expression

You build and edit your SQL expression in the large white SQL Expression box. There are a number of ways you can work with SQL expressions here:

- you can select individual components from the Fields, Functions, and Operators tree boxes at the top of the SQL Expression Editor, and the program will enter them directly in this box
- you can type your SQL expression in the box
- you can use these two methods together
- you can paste SQL expressions into the box that you cut or copied from other reports or from the Help system
- you can cut or copy SQL expression text from the box to use in other reports or in printed documents
- you can paste field values into your SQL expressions via the Browse Field Data button.

##### New

Click this button to create a new SQL expression field without leaving the SQL Expression Editor.

After you click New, the SQL Expression Name dialog box appears. Type in a name, click OK, and you are returned to the SQL Expression Editor with the name of your new SQL expression present in the Name list.

##### Save

Click Save to save the SQL expression you created.

##### Save and close

Click Save and close to save the SQL expression you created and then close the SQL Expression Editor. After clicking Save and close, you are returned to the Field Explorer.

##### Check

Use this button to test the syntax of your SQL expression and identify syntax errors (if they are any). Check is useful for interim syntax checks while you are building an SQL expression.

##### Name

The Name list shows all the SQL expression fields created for the report you are currently working on.

##### Undo

Click Undo to undo the last action performed in the SQL Expression Editor. For example, if you accidentally deleted a part of your SQL expression, you can recover it by clicking Undo.

##### Redo

Click Redo to have the program redo the last action.

##### Browse Data

This button lets you review values for the selected field and paste individual values directly into your SQL expression. When you select a field from the Fields Tree box and then click Browse Data, a dialog box appears with a scroll box listing the values for the selected field. The Browse Data dialog box shows:

- the name of the selected field at the top of the dialog box
- the data type of the field (number, string, and so on) listed immediately below the field name
- the length of the field (not the length of the value) listed below the Type Field values listed in the scroll box.

To paste a field value directly into your SQL expression, select the value and click Paste Data (or double-click the value). The value is pasted at the insertion point in the SQL expression.

##### Find/Replace

Use Find/Replace to search the SQL Expression Editor for a specified word, number, or text string.

##### Toggle Bookmark

Use Toggle Bookmark to insert a bookmark at the beginning of a selected SQL expression line. After clicking Toggle Bookmark, a blue dot appears at the beginning of the selected SQL expression line.

To remove a single bookmark, position the insertion point in the line of text marked with a bookmark and click Toggle Bookmark. The blue dot disappears from the gray column left of the text line, indicating that the line is not bookmarked.

Bookmarks make it easy to navigate through long SQL expressions, allowing you to write and edit complex SQL expressions efficiently. For example, bookmarks can be used to mark:

- frequently edited parts of an SQL expression, so you can find them easily when you want to revise the SQL expression
- major sections of an SQL expression, so you can quickly navigate through long SQL expressions
- the beginning of important SQL clauses, so you can easily jump from one to the next.

##### Next Bookmark

Use Next Bookmark to jump between bookmarks in the SQL Expression Editor.

##### Previous Bookmark

Use Previous Bookmark to jump back to the previous bookmark.

##### Clear All Bookmarks

Use Clear All Bookmarks to delete all bookmarks in the current SQL expression.

##### Sort Trees

Use Sort Trees to arrange all fields, functions, and operators in the trees in an alphabetical list.

##### Field Tree

The Fields Tree contains all the fields you can use in your SQL expressions. By default, Field Tree is activated, allowing you to view field options for your SQL expression. To hide the Field Tree, click the Field Tree button.

##### Function Tree

The Function tree contains all the functions you can use in your SQL expression. By default, the Function Tree button is activated, allowing you to view function options for your SQL expression. To hide the Function tree, click the Function Tree button.

##### Operator Tree

The Operator Tree contains all the operators you can use in your SQL expression. By default, the Operator Tree button is activated, allowing you to view operator options for your SQL expression. To hide the Operator Tree, click the Operator Tree button.

##### Help

Click Help to view on-line help information for the SQL Expression Editor.

##### Field Tree

This box displays a list of those fields that are available for use in your SQL expressions. You can resize, move, and anchor the Field Tree box, as needed.

##### Function Tree

This box displays a list of functions available for use in the SQL expression. When you select a function from the list, the program inserts it in the SQL expression at the insertion point To make your work easier, the function is inserted complete with its required syntax items (parentheses, commas, quotation marks, and so on) and the insertion point is moved to the point where you need to enter the first argument (if arguments are required).

You can resize, move, and anchor the Function Tree box, as needed.

##### Operator Tree

This box displays a list of operators available for use in the SQL expression. When you select an operator from the list, the program inserts it in the SQL expression at the insertion point.

You can resize, move, and anchor the Operator Tree box, as needed.

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com