Creating SQL Objects

To create SQL objects, use the SQL Objects (TL_SQL_OBJECT) component.

SQL objects are the most complex Time and Labor rule objects that PeopleSoft delivers. Use them to define rules that are more complicated than those accommodated by templates or actions and conditions.

SQL objects are complete SQL statements that consist of an action and may consist of one or more conditions. They can include select statements, insert statements, table joins, and subqueries. If you prefer to write free-form SQL statements, use an expression text box within the SQL object pages to enter and format the SQL statements. If you do not want to use free-form SQL statements, the SQL object pages provide guidance for each aspect of an action and condition.

If you create a SQL statement, modify a PeopleSoft-delivered object using the Copy SQL Object page to create and modify a copy. Or, use the SQL Object component to build the statement.

We use this example for discussing most pages in the SQL Object component. This SQL statement applies to the SQL Objects, Tables, Automatic Joins, Select Fields, and Where Expressions pages. It does not apply to the Expression Text, Insert, or Update pages.

INSERT INTO PS_TL_WRK02 
  (DUR
 , EMPLID
 , TL_QUANTITY) 
SELECT
   A.DUR
 , A.EMPLID
 , SUM(A.TL_QUANTITY) - %RuleTemplate()
  FROM PS_TL_IPT1 A
     , PS_TL_WRK01 B 
 WHERE A.EMPLID = B.EMPLID
   AND A.DUR = B.DUR
   AND A.SEQ_NBR <= B.SEQ_NBR
   AND A.PUNCH_TYPE IN %RuleTemplate()
   AND A.TRC IN %RuleTemplate() 
 GROUP BY A.DUR, A.EMPLID

Note: PeopleSoft recommends that you have adequate SQL training before attempting to use these pages. Use templates, actions, and conditions whenever possible.

Defining SQL Objects

For any SQL object you create, designate a name, several descriptions, and the type of object you are building. Although this component has eight pages, you see a maximum of five. The visible pages depend on the type of SQL object you are building. Here are some questions to consider when defining a SQL object:

  • What type of SQL statement do I want to create?

  • What is the driver table for the statement?

  • What is the result table for the SQL Select statement?

  • What do I want to name the resulting SQL statement?

  • What other tables do I want to join to the driver table?

  • For each table added, how do I want to join this table to the driver table?

    Do I want to do this later?

  • What fields do I want to select?

  • What fields on the driver table do I want to update?

  • How do I want to determine the scope of the Where clause statement?

In the example, we want to create a select with Insert statement. We select from TL_IPT1 and insert the results of the query into TL_TA_RESULTS. Entries on the SQL Object page provide this clause of the original example:

INSERT INTO PS_TL_WRK02
(DUR
 , EMPLID
 , TL_QUANTITY)

Setup Tasks

Creating SQL objects comprises the following tasks:

  1. Selecting the type of action and creating descriptions of the SQL statement.

  2. Creating free-from SQL text.

  3. Entering values in the record field of a particular table.

  4. Defining meta-SQL parameters.

  5. Creating expression text.

  6. Updating a record field.

  7. Designating which tables you will use in this SQL object.

  8. Creating expression text for tables.

  9. Joining tables.

  10. Creating the first clause of a Select statement.

  11. Creating expression text for the first clause of a Select statement.

  12. Creating the Where clause of your SQL statement.

  13. Creating expression text for the Where clause of a SQL statement.

  14. Copying a SQL object.

SQL Object Component

The SQL Object component changes according to the type of SQL statement you are building. Use this table to determine which pages are visible for each type of SQL statement:

Statement Type

SQL Object

Tables

Automatic Joins

Select Fields

Where Exp.

Exp. Text

Insert Fields

Update Fields

Delete

X

 

 

 

X

 

 

 

Expression

X

 

 

 

 

X

 

 

Insert

X

 

 

 

 

 

X

 

Select

X

X

X

X

X

 

 

 

Update

X

 

 

 

X

 

 

X

Page Name

Definition Name

Usage

SQL Object Page

TL_SQL_OBJECT_PNL

Select the type of action and create descriptions of the SQL statement you are creating.

Expression Text Page

TL_SQL_EXPRESS_PNL

Enter free-form SQL text.

Insert - Fields Page

TL_SQL_INSERT_PNL

Enter values in the record field of a table.

TL SQL Insert SecPNL Page

TL_SQL_INSMSQL_SEC

Enter meta-SQL parameters.

Expression Text Sec Panel Page

TL_SQL_OBJ_SECPNL4

Enter expression text.

Updating Fields Page

TL_SQL_UPDATE

Update a record field.

Tables Page

TL_SQL_OBJ_TABLES

Designate which tables to use in this SQL object.

Tables - Expression Text Page

TL_EXPRESN_SEC

Enter expression text for tables.

Select - Fields Page

TL_SQL_SELECT_PNL

Create the first clause of a Select statement.

Joins Page

TL_SQL_JOINS_PNL

Join tables.

Select Tables - Expression Text Page

TL_SQL_EXPRESS_PNL

Enter expression text for the first clause of a Select statement.

Where Expressions Page

TL_SQL_WHERE_PNL

Create the Where clause of the SQL statement.

SQL Bind Mapping Page

TL_SQL_BINDS_PNL

Enter expression text for the Where clause of a SQL statement.

Use the SQL Object page (TL_SQL_OBJECT_PNL) to select the type of action and create descriptions of the SQL statement you are creating.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > SQL Object

This example illustrates the fields and controls on the SQL Object page. You can find definitions for the fields and controls later on this page.

 SQL Object page

Note: SQL objects are not effective-dated, but they are associated with rules that are included in effective-dated rule programs.

Field or Control

Description

Save As ...

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.

Important! When you use the Save As... button to create the copy, the system saves the source object with any modifications prior to selecting the Save As ... button. You should always create the copy first, and then make changes to the object.

SQL Type

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.

Select Type

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.

Table Name

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.

Core Component

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.

Explanation

Enter a detailed description of the SQL Object.

Click the links at the bottom of this page to access other pages within this component.

Use the Tables page (TL_SQL_OBJ_TABLES) to designate which tables to use in this SQL object.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Tables

You must select Select from the SQL Type field menu for the Tables tab to appear.

In our example, TL_IPT1 is assigned an alias of A and TL_WRK01 is assigned an alias of B. The clause covered by this page is:

FROM PS_TL_IPT1 A
  , PS_TL_WRK01 B

This example illustrates the fields and controls on the Tables page. You can find definitions for the fields and controls later on this page.

 Tables page

Field or Control

Description

Update Where Clause

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

Tables

Field or Control

Description

Table Name

Select the name of the table to act upon.

Table Alias − Correlation ID

Select an alias for this table. An alias is used as another table name. It is generally shorter than the actual table name to save typing time.

Conditional Operator

This field is available if you are working with an effective-dated table and selecting an effective date option.

Effdt Options (effective date options)

This field provides predefined effective date logic to append to the join conditions. Valid values are: First, Join, Last, None.

First: Select this value to select the first effective-dated row.

Join: Select this value for the system to select the current dated row for this table.

Last: Select this value to select the last effective-dated row, even if that date is in the future.

None: Select this value for the system to not use any effective date logic in the query.

Conditional Operator

This field is available if you are working with an effective-dated table and selecting an effective date option of Join.

Effective Date Type

Select either Current or Expression. If you select Expression, the system enables the Expression Text button.

Expression Expression Text

Click to enter expression text. The Expression Text Secondary page appears.

Click the links at the bottom of this page to access other pages within this component.

Use the Select - Fields page (TL_SQL_SELECT_PNL) to create the first clause of a Select statement.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Fields

You must select Select from the SQL Type field menu for the Select Fields tab to appear.

This example illustrates the fields and controls on the Fields page. You can find definitions for the fields and controls later on this page.

 Fields page

All the options that you include in the text of the statement are available as field values. Using this page, you can create the SQL text for this part of the example:

SELECT
   A.DUR
 , A.EMPLID
 , SUM(A.TL_QUANTITY) - %RuleTemplate()
GROUP BY A.DUR, A.EMPLID

Field or Control

Description

Select Distinct

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.

Group By

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

Seq Nbr (sequence number)

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

Source

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.

Aggregate

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

Corr ID (correlation ID)

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

Field

Select the column from which to select.

Order

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.

Field Name

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.

State Variable

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

Click the links at the bottom of this page to access other pages within this component.

Use the Joins page (TL_SQL_JOINS_PNL) to join tables.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Joins

You must select Select from the SQL Type field menu for the Automatic Joins tab to appear.

This example illustrates the fields and controls on the Joins page. You can find definitions for the fields and controls later on this page.

 Joins page

You can only create inner joins in Time and Labor. You can join on any fields using custom-selecting or by selecting the key fields option. Because of platform constraints, you cannot perform three-way joins or unions using the Automatic Joins page, but you can make several selects into a working table and use the data from there. You can also use the Expression Text page to write free-form SQL text that can include three-way joins or unions.

We want to join the TL_IPT1 table to the TL_WRK01 on the EMPLID and DUR columns in the following clauses:

WHERE A.EMPLID = B.EMPLID
  AND A.DUR = B.DUR

Field or Control

Description

Left Table

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

Join Type

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.

Right Table

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

SQL Join Text

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

Click the links at the bottom of this page to access other pages within this component.

Use the Where Expressions page (TL_SQL_WHERE_PNL) to create the Where clause of the SQL statement.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Where Expressions

You must select Delete, Select, or Update from the SQL Type field menu for the Where Expressions tab to appear.

This example illustrates the fields and controls on the Where Expressions page. You can find definitions for the fields and controls later on this page.

 Where Expressions page

In the example, the statement is true where the date under report is greater than or equal to itself.

AND A.SEQ_NBR <= B.SEQ_NBR
   AND A.PUNCH_TYPE IN %RuleTemplate()
   AND A.TRC IN %RuleTemplate()

The system displays the record and correlation ID you selected for reference while creating the Logical Operator/Where clause.

Field or Control

Description

Logical Operator

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

Use Conditional Prompts

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

Expression Text

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

Left Expr Type (left expression text)

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.

Corr ID (correlation ID)

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

Left Field Name

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

Aggregate

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

Operator

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

Right Expr Type (expression type)

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

Corr ID (correlation ID)

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

Right Field Name

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

SQL Bind Mapping

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.

Use the Fields page (TL_SQL_INSERT_PNL) to enter values in the record field of a table.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Fields

You must select Insert from the SQL Type field menu for the Fields tab to appear.

This example illustrates the fields and controls on the Insert Fields page. You can find definitions for the fields and controls later on this page.

 Insert Fields page

Complete both the SQL Object and Insert Fields pages to create a complete Insert statement.

Field or Control

Description

Field Name

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

Insert Value Source

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.

Constant

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

State Variable

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

Expression text (MetaSQL)

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.

Read(Expression)

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.

Click the links at the bottom of this page to access other pages within this component.

This page does not allow you to remove a field from the list that can have a null value. PeopleTools does not allow the insertion of data into a table without specifying a value for numeric and character fields. Therefore, fields that can have a null value cannot be removed from the list.

Use the TL SQL Insert SecPNL page (TL_SQL_INSMSQL_SEC) to enter meta-SQL parameters.

Navigation:

Click the Meta-SQL button on the Fields page.

Field or Control

Description

MetaSQL

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

Parameter (X)

Note: (X) represents the number of the Parameter (X) field. The system assigns successive numbers to each Parameter field that appears.

Enter the data that the system should use to calculate the value of the meta-SQL variable. For example, if you selected the meta-SQL %DateDiff, enter the two dates for which the system should calculate the difference.

Use the Update Fields page (TL_SQL_UPDATE) to update a record field.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Update Fields

You must select Update from the SQL Type field menu for the Update Fields tab to appear.

You can update a field with a constant, the result of a subquery, or an other value. You can perform basic mathematical functions on this constant before the final update of the field.

UPDATE PS_TL_IPT1
   SET TL_QUANTITY = TL_QUANTITY −
       (SELECT DISTINCT A.TL_QUANTITY
          FROM PS_TL_WRK02 A, 
               PS_TL_WRK01 B
         WHERE A.EMPLID = B.EMPLID
           AND A.DUR = B.DUR
           AND A.EMPLID = PS_TL_IPT1.EMPLID
           AND A.DUR = PS_TL_IPT1.DUR), TL_RULE_ID = %RuleTemplate()
 WHERE PS_TL_IPT1.SEQ_NBR = (SELECT DISTINCT B.SEQ_NBR
                               FROM PS_TL_WRK01 B
                              WHERE B.EMPLID = PS_TL_IPT1.EMPLID
                                AND B.DUR = PS_TL_IPT1.DUR)
   AND EXISTS (SELECT 'X'
                 FROM PS_INSTALLATION I
                WHERE 'R' = %RuleTemplate())

This example illustrates the fields and controls on the Update Fields page. You can find definitions for the fields and controls later on this page.

 Update Fields page

Field or Control

Description

Field

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

Value Source and Value Source 2

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.

Field Name

Select the record field you want as the basis.

Math. Operator

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

SQL Object ID

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

SQL Object Bind Variable

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

Constant and Constant 2

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

Variable (2)

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

MetaSQL

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.

Meta SQL

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

Use the Expression Text page (TL_SQL_EXPRESS_PNL) to enter free-form SQL text.

Navigation:

Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Expression Text

Expression must be entered in the *SQL Type field for the Expression Text tab to appear at the top of the page.

Field or Control

Description

Format Expression Text

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

Expression

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.

Access the Expression Text page (Set Up HCM > System Administration > Utilities > Build Time and Labor Rules > SQL Objects > Expression Text).

Enter up to 254 characters in the expression text box.

This example illustrates the fields and controls on the Expression Text page. You can find definitions for the fields and controls later on this page.

 Expression Text page

Field or Control

Description

Show Text as Formatted SQL

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.