
VisualPQL Procedures		SAVE TABLE

SAVE TABLE

SAVE TABLE creates a table from data in the VisualPQL procedure table. If a table of the same name already exists, it can be replaced. Tables can be accessed by PQL, SQL and FORMS. Tables are stored on tabfiles.

The SAVE TABLE procedure creates a table and populates it with data. The definitions for the columns in the table are taken from the schema maps and the values for each row are taken from the procedure table records. One table row is created for every procedure table record.

The tabfile to contain the table must exist before the procedure is run. The CREATE TABFILE command in SQL and DBMS and the Create Tabfile... option from theTabfile menu can be used to create tabfiles.

SAVE TABLE [tabfile_name.]table_name
     [/ FILENAME   = fileid ]
     [/ VARIABLES  = varlist | ALL  ]
     [/ USERS      =  group[/pword][.user[/pword]]... ]
     [/ REPLACE ]
     [/ SORT       = [ (n) ]  variable [(A)|(D)], ...]
     [/ BOOLEAN    = ( logical_expression )  ]
     [/ SAMPLE     = fraction [ ,seed ] ]

`tabfile name`	If the tabfile into which the table is saved is not connected at the time the VisualPQL program is run, SIR2002 attempts to connect the tabfile. If a tabfile name is not specified, the current default tabfile is used.
`table name`	The table name that is created. This must be specified.
`FILENAME`	Specifies the physical filename of the tabfile. If the physical filename is the same as the tabfile name (appended with '.tbf' or the tabfile is connected, this clause need not be specified.
`VARIABLES`	Specifies the procedure variables that are the columns (variables) of the table. The order in which they are specified is the column order of the table. If this option is not specified, the default variable list is used. All variable schema information is carried over to the table that is created.
`USERS`	Specifies a list of groups and users within groups who may access the table being created. For the table being created, these users are granted all permissions of the table creator except DBA permissions. Other users have no permissions on the table. Passwords may be specified at the group or user level. See Permissions for further details.
`REPLACE`	The `REPLACE` keyword gives permission to overwrite an existing table of the same name if it exists. If the option is omitted and the table exists, the program terminates with an error message.
`SORT`	Specifies the order in which the procedure table records are sorted and written to the tabfile table. n 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. The optional seed is an integer that is used to initialise the random number generator.

Example: Creating a Database Subset

Since it is very simple to create table variables with the same definitions as record variables in the database, tables can be used as a database subset. The following program creates a table that is identical to a database record type.

RETRIEVAL
PROCESS CASES
.  PROCESS REC 1
.    GET VARS ALL
.    PERFORM PROCS
.  END REC
END CASE
SAVE TABLE  REC1BKUP
END RETRIEVAL


*** Table replaced ......... REC1BKUP
*** on tabfile ............. SQLTAB
*** no of rows ............. 20
*** no of columns .......... 4
*** variable names ......... 1. NAME
                             2. GENDER
                             3. MARSTAT
                             4. SSN
                             ........

After the retrieval is run, the procedure records are passed to the

SAVE
TABLE

procedure. The default tabfile SQLTAB is used. A new table called REC1BKUP is created. (The REPLACE option is used to overwrite the old table if it exists.) After the retrieval is finished, a summary report is produced showing the tabfile written to, the number of rows and columns written, and the variable names written to the table.