HomeStartingEnvironmentDBMSVisualPQLProceduresSQLFormsHost/APIIndex
PQL Procedures homecontents start chapter top of pagebottom of pagenext page index 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 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 the Tabfile 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 ]
tabfile name Specify the tabfile to save the table on. If a tabfile name is not specified, the current default tabfile is used. If the tabfile is not connected at the time the VisualPQL program is run, SIR/XS attempts to connect the tabfile. Note that there is no provision to specify an IDENTIFIED BY clause so, if a tabfile has groups or users defined, the tabfile must be connected at run time with a CONNECT TABFILE command.

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 or users in groups for tabfiles that have permissions. For the table being created, these groups or users are granted all permissions. Other users have no permissions on the table. If the group does not exist, it is created. Passwords may be specified at the group or user level. If the group(.user) already exists, there is no need to specify passwords. If the group(.user) already exists and passwords are specified, these become the current passwords. This is the equivalent of the SQL command GRANT ALL for the list of users where the permissions were granted by the group(.user) specified to connect the tabfile.

Regardless as to whether this clause is specified or not, the group(.user) specified to connect the tabfile has full permissions on the table. To create a PUBLIC table on a tabfile with permissions, specify USERS = PUBLIC. (For tabfiles without permissions, all tables are public and the USER clause has no affect.) See Permissions for further details on tabfile permissions.

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.

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.

homecontents start chapter top of pagebottom of pagenext page index