PQL Procedures

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

PQL Procedures

WRITE RECORDS

The WRITE RECORDS procedure produces a fixed format data file and serves as a general purpose interface to other programs. The WRITE command in VisualPQL also creates output files. The WRITE RECORDS procedure allows an output file to be written in a different sequence from the input data.

WRITE RECORDS  FORMAT    = ( format_specifications )
     [ FILENAME   = filename ]
     [ VARIABLES  = varlist | ALL ]
     [ LRECL      = number ]
     [ MISSCHAR   = char  ]
     [ SHOWMISS ]
     [ UPPERCASE     ]
     [ SORT       = [(n)] variable  [(A)|(D)] , ...]
     [ BOOLEAN    = ( logical_expression )]
     [ SAMPLE     = fraction ]

FORMAT

The FORMAT clause describes the format of the variables output to the file. Every output variable requires a format specification. The order of the FORMAT clause specifications matches the order of variables on the VARIABLES clause. The format specification list is enclosed in parentheses.

FORMAT =  (var1_fmt,var2_fmt,var3_fmt) /
VARIABLES = VAR1 VAR2 VAR3

The output record length may not exceed the value of LRECL (Logical Record Length), the default for which is 80 characters.
Numbers in the output record are right justified within the field size, strings are left justified. Data padding and spacing is with blanks.
The following FORMAT specifications may be used:

`Fw.d`	Floating point field, w characters wide, d characters to the right of the decimal point. The decimal point takes up one of the w characters.
`Iw`	Integer field, w characters wide, no decimal point.
`Aw`	Alphanumeric (string) field, w characters wide.
`Ew.d`	Exponential (scientific) notation w characters wide, d characters to the right of the decimal point. The decimal point takes up one of the w characters.
`Bw`	String, printed Backwards (reversed), right-justified and blank filled on the left.
`nX`	Skip n characters on the output record.
`Tn`	Tab to column n before writing the next variable. Tabbing may only be done to the right.
`'text'`	The specified string enclosed in quotes is written to the output record.

If several variables have the same format, a repeat count can be used, as in 5F4.1 or 3I6. Parentheses can be used to specify groups of repeating format specifications as in 3(1X,I4,2A10). Parenthetical expressions can be nested up to 10 levels deep. For example:

           VARIABLES = TEST1 TEST2 TEST3 /
           FORMAT    = 3(I3)

           VARIABLES = NAME1 TEST1 NAME2 TEST2 /
           FORMAT    = 2(A15 , I3)

If the number of variables in the variable list exceeds the number of format fields, the excess variables are output using the format over again from the last nested left parenthesis. Each time the end of the format is encountered, a new line is started in the output file.

FILENAME

Specify the filename created by the procedure. If this is not specified, the default output file is used in batch or the scrolled output buffer is written to in an interactive session.

VARIABLES

Specifies the procedure variables written to the output data file. The order in which variables are specified is the order in which they appear in the output file. If this option is not specified, the default variable list is used.

LRECL

Specifies the logical record length written to the output file produced by WRITE RECORDS. The default is 80 characters. The format clause must not specify lines that exceed LRECL characters.

MISSCHAR

Specifies the character used as the Missing or Undefined value indicator. The default is a blank. For example, to have all missing values represented by asterisks, specify:

MISSCHAR = *

SHOWMISS

Specifies that a variable's original missing values are printed for fields containing missing values. The default character is blank. Missing values are always excluded from totals - this option only affects printing.

UPPERCASE

Specifies that string values are converted to uppercase.

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 which procedure table records are used by the procedure. The procedure table records for which 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.

Example

In this example, WRITE RECORDS creates a file named SCHOOL having 2 lines for each procedure table record.

WRITE RECORDS FILENAME = SCHOOL
              VARIABLES= CASENO NAME SCHOOLNO SCHLNAME
                         AGE GRADE SEX RESP1 TO RESP5
              FORMAT   = (2(I6,A20),2I2,I1,/,5I1)

The first line of each pair contains the variables CASENO through SEX in the format (I6,A20, I6, A20, I2, I2, I1). The second line contains the variables RESP1 to RESP5 in the format (I1, I1, I1, I1, I1).