PQL Procedures

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

PQL Procedures

DIF Save File

The DIF SAVE FILE procedure creates a text file in Data Interchange Format. The DIF file can be used to transfer data to a software package that recognises DIF format.

DIF SAVE FILE    FILENAME = filename
   [ VARIABLES = varname [('header')] ...|ALL]
   [ SORT    = [(n)]  variable [(A)|(D)] ,  ....]
   [ BOOLEAN   = ( logical_expression )]
   [ SAMPLE    = fraction]
   [ TITLE     = 'text' | NOTITLE]
   [ NOHEADING ]

`FILENAME`	Specifies the filename created by the procedure.
`VARIABLES`	Specifies the procedure variables that are written to the output file. The order in which they are specified is the order in which they appear in the output file. If this option is not specified, the default variables are output. Header text may be specified as text in quotes in parentheses following the variable name. If header text is not specified, the variable name is used. Dummy columns are specified as header text that does not immediately follow a variable name. The `NOHEADING` option suppresses all headers. Multiple strings in quotes in one set of parentheses specify multi-line headers.
`SORT`	Specifies the sequence of the output. n is an integer that specifies the maximum number of records to be sorted. The default for this parameter is either the number of records in the database or the value specified in the sortn parameter and need only be specified if the number of records in the procedure table is greater than the default. The procedure table is sorted by the specified variables in variable list order. A variable name followed by (A) or (D) specifies that for that variable the sort is in Ascending order (the default) or in Descending order.
`BOOLEAN`	Specifies procedure table records used by the procedure. The procedure table records where the logical expression is true are used by the procedure. If this option is not specified, all procedure table records are used.
`SAMPLE`	Specifies that a random sample of the procedure table records are used by the procedure. The fraction specifies the percent of records used and is specified as a positive decimal number less than or equal to 1 (one). .25, for example specifies that a 25% sample be used.
`TITLE`	Specifies the title written to the DIF file. If `TITLE` is not specified, a default title is generated.
`NOTITLE`	Suppresses the default title.
`NOHEADING`	Suppresses generation of column headers.

Example 1: Using Defaults

The following creates a DIF file containing each employee's name, social security number and birth date using the variable names as the default column headers. BIRTHDAY is converted to a string in a date format before being sent to the procedure.

RETRIEVAL
PROCESS CASES
.  PROCESS REC EMPLOYEE
.    GET VARS NAME SSN
.    COMPUTE BDAY = DATEC(BIRTHDAY,'Mmm DD, YYYY')
.    PERFORM PROCS
.  END REC
END CASE
DIF SAVE FILE FILENAME = DIF.OUT /
END RETRIEVAL

When the above program is run, the following summary report is displayed:

DIF SAVE FILE Summary Report
----------------------------
Title .................. generated
No of header records ... 1
No of data records ..... 20
No of columns .......... 3

Example 2: Sorting and Using Column Headers

The following example specifies a title and custom column headers for each variable and the output is sorted by social security number.

RETRIEVAL
PROCESS CASES
.  PROCESS REC EMPLOYEE
.    GET VARS NAME SSN
.    COMPUTE BDAY = DATEC(BIRTHDAY,'Mmm DD, YYYY')
.    PERFORM PROCS
.  END REC
END CASE
DIF SAVE FILE FILENAME  = DIF.OUT /
              SORT      = SSN /
              TITLE     = 'Employee Listing' /
              VARIABLES = NAME ('Employee Name')
                          SSN  ('Soc Sec Number')
                          BDAY ('Birthday')
END RETRIEVAL

Example 3: Creating Dummy Columns

The following creates two dummy columns, the first called 'Dummy One' and the second (between SSN and BDAY), 'Dummy Two'

RETRIEVAL
PROCESS CASES
.  PROCESS REC EMPLOYEE
.    GET VARS NAME SSN
.    COMPUTE BDAY = DATEC(BIRTHDAY,'Mmm DD, YYYY')
.    PERFORM PROCS
.  END REC
END CASE
DIF SAVE FILE FILENAME  = DIF.OUT /
              SORT      = SSN /
              TITLE     = 'Employee Listing' /
              VARIABLES =      ('Dummy One')
                          NAME ('Employee Name')
                          SSN  ('Soc Sec Number')
                               ('Dummy Two')
                          BDAY ('Birthday')
END RETRIEVAL