Using the SQL Editor

This chapter provides an overview of the Structured Query Language (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 editor window’s title bar 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, 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, there is also a drop-down database list at the upper left. For SQL definitions, you can also use a drop-down effective date list 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

Access the definition properties for the SQL definition by either:

• Pressing ALT+Enter.

• Selecting File, Definition Properties.

• Right-clicking in the definition and selecting Definition Properties.

Use general properties to specify a description for the SQL definition as well as additional comments. The description appears in PeopleSoft 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 PeopleSoft Application Designer, or you can use the SQL class in PeopleCode. SQL definitions are upgradable, and you can add them to a project. The following illustration shows a SQL definition:

To create a SQL definition:

1. From PeopleSoft 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.

      You can also select the SQL definition, right-click and then select Object Properties, or press ALT + ENTER.

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

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

4. Add 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:

To access the SQL editor with records:

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 prior to 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.

Considerations with View SQL and Union Statements

If you've selected the Platform Compatibility Mode check box under PeopleTools, Utilities, PeopleTools Options you can not use Union statements in a view. If you try to specify a Union Select statement, you won't be able to save the SQL definition.

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:

To 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. You can use the same functions as with the PeopleCode editor: cut, paste, find, replace, and so on.

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

SQL Editor shortcut menu

The following functions are available for the SQL editor, but are not available for the PeopleCode editor.

For example, using Resolve Meta-SQL, the following code expands as follows:

%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC, PSAESECTDEFN XYZ)

See Also

Writing and Editing PeopleCode