Running SQL Alter

The primary purpose of the Application Designer SQL Alter function is to bring SQL tables into accordance with PeopleTools record definitions. You can run SQL Alter in an audit-only mode that alerts you to discrepancies between record definitions and SQL tables, but that doesn't actually perform an alter.

To audit tables or views:

  1. In Application Designer, choose the records that you want to audit.

    You have the option of auditing the active record definition, the selected records in the project workspace, or all the records that are in the current project.

  2. Select the Build menu and select the appropriate option for the records that you want to audit.

    If you're auditing an open record definition, choose Build, Current Object. If you select one or more records in the project workspace, you can select Build, Selected Objects. If you want to audit all records in the current project, select Build, Project.

    The Build Scope shows a list of all the records that are affected, or audited in the case.

  3. Select Alter tables as the Build Option and select Build script file as the Build Execute option.

  4. Click Settings and choose the Alter tab in the Build Settings dialog.

  5. In the Alter Any group box, select the situations for which you want an Alter performed.

  6. Select the Scripts tab.

    You use the Scripts tab to specify the output for the build scripts in one file, in two files, where the file is generated, and so on.

  7. Select Write Alter comments to script.

    Performing alters with this option enabled adds comments to the SQL script about what fields are being manipulated.

  8. Choose the other script file options.

  9. Click OK to close the Build Settings dialog and return to the Build dialog.

  10. Press Buildon the Build dialog.

The SELECT statements that are produced by auditing with SQL Alter deal with inconsistencies between PeopleTools tables and SQL in the definition of tables or columns. A SQL table is equivalent to a record in Application Designer, and a column is equivalent to a field.

To fix problems that are found in the system tables and columns, you need to know how PeopleSoft field types correspond to SQL data types:

Application Designer Field Type

SQL Data Type

SQL Description

Character

CHAR

Alphanumeric; fixed length.

Long character

LONGVAR

Alphanumeric; variable length.

Date

DATE

Dates; stored as fixed length; displayed in various formats.

Number or signed number

SMALLINT

Numeric; integers only (no decimals); 1 to 4 digits (and 5 digits if RawBinary).

Number or signed number

INTEGER

Numeric; integers only (no decimals); 5 to 9 digits (and 10 digits if RawBinary).

Number or signed number

DECIMAL

Numeric; either (1) 10 or more digits or (2) contains decimal positions.

Note: In Application Designer, if a field is specified as required, or if a field is numeric and does not have a format of Phone, SSN (social security number), or SIN, you need to initialize the starting value of the column and specify the NOT NULL attribute in SQL.