Accessing the SQL Editor

This section discusses how to:

  • Create SQL definitions.

  • Access SQL definition properties.

  • Create dynamic view or SQL view records.

  • Access the SQL Editor from Application Engine programs.

You access the SQL Editor differently for each definition type.

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:

This example illustrates a SQL definition with an effective date.

 Example of a SQL definition with an effective date

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 select the Show Effective Date check box.

      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 validates and formats it when you save the SQL definition.

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.

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:

This example illustrates the SQL Editor for SQL view record definition.

 SQL Editor for SQL view record definition

To access the SQL Editor from 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.

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 SQL code in the SQL Editor:

Example of an Application Engine program in the SQL Editor window

To access the SQL Editor from 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.