Reference Commands › pdm_deref--Dereference ASCII Data

pdm_deref--Dereference ASCII Data

pdm_deref processes ASCII-formatted input to exchange data found in one database table for data found in another database table. It can be used to create files compatible with pdm_userload from a non-CA SDM database or spreadsheet. It can also be used to create reports or output files for a non-CA SDM database or spreadsheet.

Important! Do not use pdm_deref if you are unfamiliar with SQL. The directories "export" and "import" in $NX_ROOT/samples (UNIX) or installation-directory\samples (Windows) contain a standard set of specfiles for viewing.

Syntax

This command has the following format:

name pdm_deref -s specfile [-c|-e|-r] [-d] [-h] [-n] [-u] [-v] <filename

-s specfile

(Required) Specifies a file that defines which data is exchanged and the conditions under which it changes.

Specfile contains a list of SQL commands in the following format (note that "att" means attribute and "atts" means attributes):

Deref

{
input = <list of "from" atts from input file>
output = <list of "to" atts for output file>
rule = "SELECT <atts used to fill output atts> \
  FROM <table name>  \
  WHERE <att from table name to match input 1> =?\
  AND <att from table name to match input 2> = ? \
  OR <att from table name to match input 3> =?"
}

-c

Produces comma-separated value (CSV) output, such as:

"field1","field2","field3"

The -c, -e, and -r output format options are mutually exclusive.

-e

Produces comma-separated value (CSV) output with embedded double quotes escaped by another double quote. For example:

"Text with a "quoted string" in it".

The -c, -e, and -r output format options are mutually exclusive.

-r

Produces left-justified output in the formats if the column labels are not supplied in the input file:

"label":"value"

or

"value"

This option is intended for use with line printers, for example:

Field_Name: Field Value

The -c, -e, and -r output format options are mutually exclusive.

-d

Produces diagnostic information.

-h

Displays help and usage information.

-n

Specifies that you do not want to treat 0 valued foreign keys as NULL. This argument should be used only under special circumstances when recovering a damaged database.

-u

Produces output without headers.

-v [1|2]

Specifies verbose mode. The value 1 is the brief mode. The value 2 prints the progress messages to the log file.

filename

(Optional) Specifies the ASCII-formatted input file to be processed. If omitted, stdin is used.

Restriction—Valid on UNIX only

Before using pdm_deref on UNIX, the $NX_ROOT environment variable must be set to the path of the CA SDM installation directory, and the PATH environment variable must include $NX_ROOT/bin.

Example: Using pdm_deref to Set Up Data for Input

This example shows how you can use pdm_deref to set up data for input.

Given the following data in the ca_location table:

id    location_name_name
86873FA40BA4234A8CF7A418D7C8B2DB    "Boulder NCC"

the following statement in the specfile:

Deref
{
input = place
output = location_uuid
rule = "SELECT id FROM ca_location WHERE location_name=?"
}

would change the following input:

last_name, first_name, place
{"Potts", "Elmore", "Boulder NCC"}

to the following output, which can be loaded into the ca.contact table with pdm_userload:

last_name, first_name, location_uuid
{"Potts", "Elmore", "86873FA40BA4234A8CF7A418D7C8B2DB"}

Example: Using pdm_deref to Set Up Data for Output

This example shows how you can use pdm_deref to set up data for output.

Given the following data in the ca_contact table:

id  last_name first_name
"69499D5A2424884887E62EC9823F5E47"    "Potts"    "Elmore"

the following statement in the specfile:

Deref
{
input = primary_contact_uuid
output = firstname, lastname
rule = "SELECT first_name, last_name FROM ca_contact
WHERE id=?"
}

would change the following input:

location_name, primary_contact_uuid
{"Boulder NCC", "69499D5A2424884887E62EC9823F5E47"}

to the following output, which can be printed or sent to a spreadsheet:

location_name, firstname, lastname
{"Boulder NCC", "Elmore", "Potts"}

More information:

pdm_extract--Extract Data from Database