Creating SQL Objects

Select to duplicate an SQL object by giving it a different name, or create an SQL object that is similar to an existing object or a PeopleSoft-delivered SQL object. This function replaces the Copy SQL Object page from previous releases of PeopleSoft Time and Labor.

Select the SQL action that this SQL object should perform. Valid options are:

Delete: Use to create a Delete statement. If you select this option, the component displays only the SQL Object and Where Expressions pages. If you create a Delete statement to clear a table, we recommend that you create a truncate action step on the Define Rule Steps page instead.

Expression: Use to enter free-form SQL expression text. If you select this option, the component displays only the SQL Object and Expression Text pages.

Insert: Use to create an Insert statement. If you select this option, the component displays only the SQL Object and Insert Fields pages.

Select: Use to create a Select statement. You use a Select statement to query. If you select this option, the component displays the SQL Object, Tables, Automatic Joins, Select Fields, and Where Expressions pages. Also, the Select Type field appears.

Update: Use to create an Update statement. If you select this option, the component displays only the SQL Object, Update Fields, and Where Expressions pages.

This field appears if you chose Select in the SQL Type field. Select the type of action to use. Valid values are: State, Subquery, w/Insert.

State: Use to select fields into a state record. A state variable is from the AE State records. Use to retrieve an exception description to be used in multiple locations within the AE program. Store the exception description in a state record so that you don't have to issue a SELECT every time you need it. A state record field is equivalent to a variable in a procedural language.

Subquery: Use to use the Select statement you create as a subquery in another query.

A subquery must be bound to an outer query. Time and Labor does not limit the number of nested subqueries, but the database platform might have constraints.

w/Insert: This option is like a SQL insert with select. It is only visible if Select is chosen.

Use a SQL insert to insert a list of values into a table. Select w/Insert to select fields from a source table to insert into a target table.

This field appears if you selected w/Insert in the Select Type field. Choose the table against which the statement is made. The drop-down list box contains all tables loaded on the Working Tables page.

This field is display-only and system-maintained. Time and Labor delivers several SQL objects that are used in templates. If the PeopleSoft system created the object that you are viewing, the system selects this check box to signify that you did not build the object. If you created this object, the system clears this check box. You cannot modify or save this object, but you can copy it and modify the copy.

Enter a detailed description of the SQL Object.

Click to update the Where clause of the SQL statement. The system automatically generates a SQL Where clause based on the effective date option.

Select to exclude duplicate results of the query. Selecting this check box is a method of using the SQL DISTINCT command.

DISTINCT is not valid with some aggregates (COUNT(*), MIN, MAX), but is valid with COUNT if you specify columns.

This value is automatically generated when there is an aggregate function.

Enter a sequence number for this row. The system reorders all entries on this page according to this field.

Select the source of the fields to include in this statement. The value in the Source field determines which other fields are visible. Valid options are:

Constant: Select this value to specify a constant. The Order by and Field Name fields appear on the page.

Expression: Select this value to enter expression text. The flashlight button appears.

Recfield: Select this value to specify a record field.

Statevar: Select this value to specify a state variable.

Template: Select this value to specify a template.

To perform an aggregate function, select that option here. Valid options are: (none), AVG, COUNT, COUNT(*), MAX, MIN, and SUM.

Select the alias of the table to specify for this sequence number.

Select the column from which to select.

The order field can be used to control the order of the data returned. It is applicable only to statements with a sub-query Select Type.

This field is only applicable to statements with a Select.Insert Select Type. This field refers to the target field on the table that is receiving data.

This field only applies to the Select Type of Into State. This field indicates the State Variable that the selected field should go into.

Select (by alias) the first table in your join. Valid options are: (none), A, B, C, D, E, F, G, H, I, J.

You can join in various ways. Valid options are: Fields and Key Fields.

Select Fields to select any record fields in the five drop-down list boxes that appear.

Select Key Fields to select from the key fields that appear in the drop-down list box.

Select (by alias) the second table in the join. Valid options are: (none), A, B, C, D, E, F, G, H, I, J.

This field displays the text of the join. The system uses the meta-SQL construct %JOIN.

Select an operator in this field. Valid values are: (none), AND, HAVING, NONE, OR, and WHERE.

Select to build the clause in the page instead of entering expression text for this clause. The system selects this check box automatically.

This field holds 254 characters. Only a small SQL expression or subquery can be entered into this field.

Valid values are: (none), Binding, Constant, Meta-SQL, Recfield, SubQuery, Template, and Variable.

If you select Meta-SQL, the %SQL button appears on the page.

Select the alias of the table to use. This field appears only when Recfield is selected for the left expression type.

Select a column name of the table to use for the left expression.

If you select Recfield, the Aggregate field appears. Valid values are: NONE, MIN, MAX, AVG, SUM, COUNT, and COUNT(*).

Enter an operator. Valid values are: (none), <, <=, <>, =, >, >=, EXISTS, IN, NOT EXIST, and NOT IN.

Enter a right expression type. Valid values are: (none), Binding, Constant, Meta-SQL, Recfield, SubQuery, Template, and Variable.

Select the alias of the table to use. This field appears only when Recfield is selected for the right expression type.

Select a column name of the table to use for the right expression.

Click the icon to access the SQL Bind Mapping page, where you can enter data for a bind. This icon is available when the left or right expression type select is MetaSQL or Subquery. For MetaSQL, it presents a page where Meta SQL parameters can be entered. For subquery, it presents a page where SQL Bind variables can be given values.

Select the column in which you want to enter the value.

Select the source of the value. Valid values are: Constant, Expression, Meta-SQL, Template, and Variable. The page is modified according to the option you select.

If you selected Constant in the Insert Value Source field, this field becomes available. Enter a constant in this field.

Enter the variable to use. This field is available if you selected Variable in the Insert Value Source field.

This icon is displayed for the Insert type of SQL statement, when the Value Source is Meta-SQL. Click it to select a meta-SQL option. The Insert Value using MetaSQL page is displayed.

This icon is displayed for the Expression type of SQL statement, when the Value Source is Expression.. Click it to enter expression text. The Insert Expression page appears.

Select the meta-SQL variable to include in the Insert statement.

Select the record field to update. The result of the subquery or math operation is placed in the specified field.

Select the source of the value to act upon. Valid options are: Constant, Another Field, Meta-SQL, Subquery, Template, Variable.

Only one of the Value Source fields can be set to Subquery at a time. When you choose Subquery, the SQL Object Bind Variables link becomes available.

Select the record field you want as the basis.

To perform a mathematical function, select the operator. If you do not want to perform any mathematical functions, select (none). Valid options are: -, +, *, /.

This field is used to add a subquery to a Set clause.

Link to the SQL Bind Mapping page, this is available when Value Source is set to Subquery.

This field is available when you set the Value Source or Value Source 2 field to Constant.

you can enter a state variable in this field when the Value Source 2 is set to Variable.

You can enter a value in this field when the Value Source field is set to MetaSQL. This field is not available for any values in the Value Source 2 field.

This icon links to the MetaSQL Parameters page, it is available when the Value Sourcefield is set to MetaSQL.

Click to format the SQL text in the Expression field. You must enter your statement before clicking the button.

This field is an expression text box in which to type SQL statements. You can also paste text from other applications. The system stores anything you enter and does not check for errors. Before entering your statement and saving the page, run the SQL statements through an interactive SQL tool.

You can click this button to view the formatted SQL statement. The formatted text is shown on the same page as view-only text. The name of this button then changes to Show Entered Text, which enables you to view and edit the text as it was originally entered.