Using PeopleSoft Data Mover Command Modifiers

The following commands enable you to modify a PeopleSoft Data Mover command to limit its scope, rename the item being processed, or control error messaging.

Syntax

{IMPORT | REPLACE_ALL} record   AS table_name;

Description

Changes the name of a record and then imports it. When using this modifier, keep the following points in mind:

If used with an IMPORT, the record is not imported if the table name specified in the IMPORT command already exists in the database.
When using the AS command modifier, you can specify either the record or table name for the record or table specified preceding the AS.
However, you must always specify the table name (not the record name) for the record or table specified following the AS. The name specified following the AS is the actual name that is used for the table to be created.
This modifier is not supported for records containing trigger definitions.

Parameters

IMPORT and REPLACE_ALL

Example

The following example imports a new record or table originally named PS_JOB and creates it as PS_PROCESS:

IMPORT job
  AS ps_process;

Also correct:

IMPORT ps_job
  AS ps_process;

Incorrect:

IMPORT ps_job
  AS process;

Incorrect:

IMPORT job
  AS process;

The last two examples are incorrect because process is specified, instead of ps_process. This means that the table created is named PROCESS, but it should be named PS_PROCESS to comply with the convention that all non-PeopleTools tables have the prefix PS_.

The table name that you specify following the AS command modifier should not have more than 18 characters (including the ps_ prefix). Specifying a table name that is greater than 18 characters invokes the following error message: Error: Unable to process create statement.

When you import a record in this way, it is only created in the system tables, not in the PeopleSoft tables. You must also create the record in the PeopleSoft tables, such as PSRECDEFN.

To create a table after running the IMPORT command:

Launch Application Designer.
Create or clone the new record.
Using the job and process example from the previous discussion, you would open JOB and then select File, Save As and rename the record to PROCESS.

Note: The PS_ prefix does not appear in Application Designer.
Select Build, Current Object.
In the Build dialog box, select Create Tables under Build Options.
You may also want to make sure that all the appropriate options are set on the Build Settings tabs.

Syntax

SET IGNORE_DUPS; IMPORT {record | *};

Description

Ignores duplicate-row error messages from the database. The IMPORT process continues despite any duplicate-rows errors in the output window and log file. When IGNORE_DUPS is set, bulk loading, the ability to load more than one row at a time, is turned off. By default, bulk loading is on and inserts up to 100 rows into a table at a time. Because turning off bulk loading slows performance, you should use this feature only when required.

Note: SET IGNORE_DUPS is only valid in bootstrap mode.

Parameters

IMPORT

Syntax

SET UPDATE_DUPS; IMPORT {record | *};

Description

On command, PeopleSoft Data Mover imports a new row and updates an existing row.

Note: This command is valid for both bootstrap mode and regular mode. In regular mode, if the table is identified as a language table, the system automatically resolves and swaps the base and related language tables.

See IMPORT.

Parameters

IMPORT.

Syntax

Data Mover Command {record | *} WHEREcondition(s)[;var#1_type,_var#1_value,var#2_type, var#2_value,...var#n_type,var#n_value];

Note: In an EXPORT statement, the WHERE modifier must be on the same line as the EXPORT command.

Description

Retrieves a partial set of rows from the data source. The data source could be a data file or the database. The syntax and conditions of a Data Mover WHERE clause are similar to a WHERE clause in SQL. For EXPORT, you can write the WHERE clause with comparison operands in-line or as bind variables. For IMPORT the WHERE clause only supports comparison operators as bind variables. Parentheses are not support in IMPORT. You can also use nested SELECT statements.

Warning! When comparing string or character values, use only US-ASCII (seven-bit ASCII) values. Characters beyond this range can produce errors in the export file.

Parameters

EXPORT

IMPORT

Example

Here's an example of a WHERE clause using both an inline operand and bind variables in an EXPORT script:

EXPORT JOB WHERE
  EFFDT > :1 AND 
  HOURLY_RT > :2 
  AND GRADE = 'ADV';DATE,1994-01-01,NUMBER,100;

There are no single or double quotation marks around the bind data, as they are not necessary, and dates are formatted as YYYY-MM-DD. The valid data types for binding are CHAR, NUMBER, DATE, TIME, DATETIME, LONG, and IMAGE. Not all database platforms support LONG or IMAGE data types in the WHERE clause, so you should not use WHERE clauses with these data types.

The following operators are supported in an import WHERE clause: =, < >, <, >,< =, > =, and simple uses of AND and OR. For example, in the following formula, if A, B, and C are true, or if D is true, or if E is true, then the whole statement is true

WHERE 
  A = :1 AND B = :2 AND C = :3 
  OR D = :4 
  OR E = :5;NUMBER,10,NUMBER,20,NUMBER,30,NUMBER,0,NUMBER,1;