Using the SQL Editor

This chapter provides an overview of the SQL editor window and discusses how to:

• Access SQL definition properties.

• Access the SQL editor.

• Use the SQL editor.

See Also

Using the PeopleCode Editor

Understanding the SQL Editor Window

Use the SQL Editor to create SQL for SQL definitions, record views, and Application Engine programs.

The SQL Editor and the PeopleCode editor interfaces are similar. You can add, delete, and change text; you can use the find and replace function; and you can validate the SQL. When you save a SQL definition, the code is automatically formatted (indented and so on) the same as it is for a PeopleCode program. You can select the colors for displaying keywords, comments, operators, and so on. You can also specify word wrap options.

See Using the PeopleCode Editor.

The title bar of the editor window displays either the name of the SQL definition or the name of the component that contains the SQL. For example, if the SQL statement is part of an Application Engine program, then the names of the program, the section, the step, and the action are listed in the title bar, as shown in the following example:

The editor window consists of the main edit pane. For SQL definitions and SQL used with records, a drop-down database list appears at the upper left. For SQL definitions, a drop-down effective-date list is available at the upper right.

Note. When you make a selection from either drop-down list box, your selected entry has a yellow background, indicating that you must click the edit pane before you can start typing.

Accessing SQL Definition Properties

Do one of the following to access the definition properties for the SQL definition:

• Press ALT+Enter.

• Select File, Definition Properties.

• Right-click in the definition and select Definition Properties.

Use general properties to specify a description for the SQL definition as well as additional comments. The description appears in Application Designer search lists.

Use the advanced properties to display an effective date with the SQL definition.

Note. The Audit SQL field on the Advanced Properties tab is not used.

Accessing the SQL Editor

This section discusses how to:

• Create SQL definitions.

• Create dynamic view or SQL view records.

• Access the SQL editor from Application Engine programs.

You access the SQL editor differently for each type of component.

Creating SQL Definitions

A SQL definition contains SQL statements, which can be entire SQL programs or just fragments that you want to reuse. You can access, create, change, or delete SQL definitions using Application Designer, or you can use the SQL class in PeopleCode. You can upgrade SQL definitions, and you can add them to a project. The following example shows a SQL definition:

To create a SQL definition:

1. From Application Designer, select File, New, SQL.

2. Specify the database type to associate with the SQL definition.

   You can associate more than one database type with a single SQL definition. In PeopleCode, you can specify the appropriate database type for the program. However, at least one of the SQL statements must be of type Default.

3. (Optional) Specify an effective date.

   To specify an effective date with your SQL definition:

   1. Access the object properties by selecting File, Object Properties.

      Alternatively, select the SQL definition, right-click it, and then select Object Properties, or press ALT + ENTER.

   2. Click the Advanced tab, and then click Show Effective Date.

      When you click OK, the SQL definition shows a date in the right-hand drop-down menu.

4. Enter the SQL code.

   You do not need to format your code. The SQL editor formats it when you save the SQL definition.

See Also

Using PeopleSoft Application Designer

SQL Class

Creating Dynamic View or SQL View Records

When you create a SQL view or dynamic view record definition, you enter a SQL view Select statement to indicate the field values that you want to join and the tables that contain the field values. You do this in the SQL editor, as shown in the following example:

Access the SQL editor with record definitions.

1. Open or create a dynamic view or SQL view record definition.

2. Select the Record Type tab.

3. Click the Click to Open SQL Editor button.

   You can select a database type, but not an effective date, from the SQL editor for dynamic view and SQL view record definitions.

Note. You must be sure to save record definitions of the SQL View type before opening the SQL editor. Once the SQL editor is open, the Save options are disabled and inaccessible. If you do not save your changes before opening the SQL editor, you may lose your work.

See Also

PeopleTools Options

Accessing the SQL Editor from Application Engine Programs

You can access the SQL editor from the following action types:

• Do Select

• Do Until

• Do When

• Do While

• SQL

The following example shows an Application Engine program in the SQL editor:

Access the SQL editor in an Application Engine program.

1. Open the Application Engine program.

2. Select the action.

3. Either right-click and select View SQL, or select View, SQL.

   Select the database type and effective date for this SQL in the section, not in the SQL editor.

Using the SQL Editor

The SQL editor works similarly to any other text editor. Many of the same functions are available as in the PeopleCode editor, for example, cut, paste, find, and replace.

When you right-click in an open SQL editor window, you see available functions for the SQL editor:

SQL editor shortcut menu

This table describes the functions that are available in the SQL editor but not the PeopleCode editor:

This example, using Resolve Meta-SQL, shows how the following code expands:

%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC, PSAESECTDEFN XYZ)

See Also

Writing and Editing PeopleCode