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.
Using the PeopleCode Editor
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.
Do one of the following to access the definition properties for the SQL definition:
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.
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.
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:
From Application Designer, select File, New, SQL.
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.
(Optional) Specify an effective date.
To specify an effective date with your SQL definition:
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.
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.
Enter the SQL code.
You do not need to format your code. The SQL editor formats it when you save the SQL definition.
Using PeopleSoft Application Designer
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.
Open or create a dynamic view or SQL view record definition.
Select the Record Type tab.
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.
You can access the SQL editor from the following action types:
The following example shows an Application Engine program in the SQL editor:
Access the SQL editor in an Application Engine program.
Open the Application Engine program.
Select the action.
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.
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:
You do not need to format your SQL statements; you only need to use the correct syntax. When you save or validate, the system formats the code according to the rules in the PeopleCode tables, no matter how you entered it originally. It automatically converts field names to uppercase and indents statements. The resulting look of SQL is consistent with other programs in the system.
If the SQL contains meta-SQL, select Resolve Meta SQL to expand the meta-SQL statement in the output window. The expanded meta-SQL appears in the Meta SQL tab.
You can delete standalone SQL statements. This menu item is not enabled with SQL statements that have a database type of Default with no effective date, or for statements that have a database type of Default and an effective date of 01/01/1900.
This example, using Resolve Meta-SQL, shows how the following code expands:
%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC, PSAESECTDEFN XYZ)
Writing and Editing PeopleCode