|
|
SELECT is a utility program that uses a describe file to interactively access a database. It can select on any field in the database with a powerful query language and can sort the selected records into any order based on up to ten fields. SELECT uses an immediate mode compiler to generate a program that performs the query and as a result, is far faster and is far less CPU intensive than 4GL relational products that are basically interpreters.
SELECT works by allowing you to establish a collection of records using the FIND command. Each FIND command creates a new version of the collection. This allows you to undo a selection and return to a previous selection by using the REVERT command to delete the latest version of the pointer file.
SELECT also allows you to create and store Views, which are selected subsets of fields in the record. Views control which fields are displayed or updated by other commands. This is particularly useful as the PRINT VIEW command (or DO) will cause the selected fields to be displayed using the current collection of records.
Additionally, fields can be totaled and up to nine level breaks can be set for page breaks and sub-totals.
The REPORT command is a full-function report writer that allows extensive control over the formatting of a report.
There are a number of symbols that control the initial environment for SELECT. The first three are generally set up in a user's LOGIN.COM, while the rest are used by SELECT to remember information between runs.
DSI_SELECT_MODE - This symbol can be defined as "COBOL", "COUNT" or "COBOL/COUNT". If it contains COBOL then dashes inside field names will be converted to underscores so that COBOL names are translated to the describe file format. If it contains COUNT then the records in a PRINT command will be numbered. These modes can also be changed with the SET and RESET commands.
DSI_SELECT_MESSAGE - If this symbol is set to "FALSE" then the various status messages that SELECT generates will be suppressed. This is useful when SELECT is running from a Web server and SYS$OUTPUT is used to send in formation to a remote client.
DSI_DEFAULT_QUEUE - This symbol defines the default queue name to be used if just the keyword QUEUE is specified in a PRINT statement.
DSI_DEFAULT_FORM - This symbol defines the default form name to be used if no FORM is specified in a print statement.
DSI_DEFAULT_DSC* - These symbols are used as a default value for the describe file prompts. They are set to the describe file names entered so when SELECT is run again, it will remember the describe files last used.
DSI_DEFAULT_DB* - These symbols are used as a default value for the data base prompts if the describe files are not changed from the last time SELECT was run. They are set to the database names entered so when SELECT is run again, it will remember the last database used. However, if the describe file name is changed, then the default database name will be taken from the describe file.
SELECT can be run by entering 'RUN DSILIB:SELECT', "RUN DSILIB:SELECT_RO", or "RUN DSILIB:SELECT_READALL". Depending on how your account is set up, you may only have access to one version of SELECT and it may be on a menu if your account does not get to the DCL prompt. See you system manager for more details about how your account is set up.
You can also set up a symbol in your LOGIN.COM to run select as follows:
$ SEL*ECT == "$DSILIB:SELECT.EXE"This allows you to run SELECT by entering "SEL GL_DSC:COMPANY" to start SELECT and to specify the first describe file.
DSILIB:SELECT.EXE is the full version that allows adding and changing records. DSILIB:SELECT_RO.EXE is a read-only version. DSILIB:SELECT_READALL.EXE is also a read only version but is installed with extended access capabilities. The last version requires an identifier to be set up for each user to gain access to the program.
If you are running the read/write version of SELECT and the logical DSI_SELECT_ENVIRONMENT is defined and its value does not contain the word TEST in it, SELECT will issue the message "Warning - You are in production environment >name<" and the ADD, UPDATE and DELETE commands will be initially locked out (See the UNLOCK command). This is a precaution to remind you that you now have access to production data.
The first screen allows you to choose which databases you want SELECT to access.
Digital Synergy, Inc - SELECT V5.18
Describe File ____________________________________________________________
Database
Translations
Describe_file
Database
Tran Name
Describe_file
Database
Tran Name
Describe_file
Database
Tran Name
Describe_file
Database
Tran Name
Default :DSI_DSC:CHANGE_TRANS
F10=Exit Select
Enter the name of the Describe file that has the field definitions for the database you want to select records from. The '*' wild card character may be included in the file name for a selection list of all matching .DSC (Describe) files. For example, if there is a logical AR_DSC that points to a directory, then entering AR_DSC:* will show all the describe files in that directory.
Press RETURN on this field when all translation files have been entered.
If you are re-entering SELECT, then the display will default to the last databases you used. Press DO to accept the default values and go to the SEL> prompt.
SELECT now searches for a file ".VIEW" where the name is the same as the database. If this file is found, it is loaded as the current view.
If there was a collection of records previously built for the database selected then this collection is reopened and made the current collection. This allows you to exit SELECT and re-enter without losing your context in a file. The name of the temporary file that contains the current collection is displayed by the SHOW STATUS command. When you exit SELECT, the logical DSI_POINTER_FILE is defined as the name of the current collection
SELECT now asks for a command with the prompt 'Sel> '. Valid commands are given below. If an invalid command is entered, SELECT displays a list of the valid commands. Commands may also be executed from a file by entering @ at the select prompt. If the file contains a line starting with a $ and containing the word SELECT, it will be interpreted as an OPEN command and will close the current files and open the opens specified by the file being read. This allows a command file that would run SELECT to be used without having to exit SELECT.
If EXIT, QUIT, F10 (Exit), Control-C or a Control-Z is entered, SELECT will exit.
SELECT also stores the last 20 commands entered in a buffer. Previously entered commands can be recalled in the same manner as DCL, using either the up and down arrows or the RECALL command.
In the following sections optional parameters are shown in square brackets ("[]"). For example the command DELETE [ALL] may be entered as either "DELETE" or "DELETE ALL".
Parameters in braces ("{}") are required, but the value entered may be selected from the list elements separated by a vertical bar (" | "). For example the command RESET {COBOL | DEBUG} may be entered as "RESET COBOL" or "RESET DEBUG" only.
Any parameter enclosed in angle brackets ("") is replaced with the item specified. For example WRITE may be entered as "WRITE COMPANY.VIEW".
Note that the ADD, DELETE and UPDATE commands are not available in the read- only versions of SELECT.
This command will add a record by prompting for the new value of each field that is set to W (write). Note that if you do not enter a value for a field, it will default to the contents of the last record selected or all ASCII nulls if no previous record was selected. Also,if the file has variable length records, then the record length used will be the length of the last record selected or the maximum record length in the describe file if no previous record was selected. This can cause data loss or truncation if the last record selected was shorter than the record you are trying to add.
This command does not work in the read-only versions of SELECT.
This command is also initially locked out if you are in a production environment (see section and the UNLOCK command).
This command rings the keyboard bell n times. If n is not specified or is less than 1 then one beep is generated. This command is useful as a type ahead command to notify you of the completion of a long query.
The break command is used to set level breaks for the PRINT command. Each time this command is used, you must specify the entire set of level breaks you want to use, starting with the most frequent break. You can set up to 9 level breaks. Level breaks can also be set using the VIEW command.
This command combines the two latest collections into a new single collection. Note that the two collections are simply appended to each other and if a record exists in both collections, it will be represented twice in the new collection.
This command will delete the current record if just DELETE is entered. The cur rent record is set by the SELECT command. If you enter DELETE ALL then you are prompted 'Sure delete all '. If you enter 'Y' then all records that have been selected will be deleted. Note that this deletes only records that have pointers built to them.
This command does not work in the read-only versions of SELECT.
This command is also initially locked out if you are in a production environment (see section and the UNLOCK command).
This command shows all fields that are flagged either R or W in the current view for either the current record selected or all records. The dump command is similar to the LIST command, except that the hexadecimal value for each byte will be displayed vertically below the field value. For example if a field contains the value "ABC" then the DUMP command will display the following:
10) FIELD_NAME: ABC@
^
4440
1230
This shows that the first byte contains the character "A" and has a hexadecimal value of 41. Note that the last character is an ASCII null and is shown by an @ with a ^ under it. The ^ signifies that this is a non-printable control character.
This command will cause SELECT to exit.
This command creates a comma delimited file using the current view, if
VIEW is specified, or the fields in the field list. The output file can
then be loaded into programs that accepts comma delimited data such as
spreadsheets or other databases.
If TITLE is specified, then the first line of the export file will
contain the names of the fields being exported.
If a file name is specified using the ON, then the output will be sent
to the file. Otherwise the output is sent to the screen.
If SUMMARY is specified, then one record will be output each time the first level break occurs. Any other level breaks do not affect the export command. If a field has been flagged as a total then the field is replaced with the total, otherwise the value from the last record before the break is used.
Note that in a comma delimited format, all string fields are enclosed in quotation marks while numeric fields are not.
This command is used to find records in the current database. See section on the FIND command for a complete description.
The HEADING command changes the column heading used by the PRINT command. SELECT will build up to three heading lines by breaking the heading test at an underscore. For example a heading of GRAND_TOTAL produces two heading lines with GRAND on the first and TOTAL on the second. Note that headings can contain spaces so a heading of "THIS YEAR_PAYABLE" produces two lines of headings consisting of "THIS YEAR' for the first line and "PAYABLE' for the second line.
If no heading is given then the heading for that field will be blanked out.
The default heading is the field name.
When using the PRINT command, fields are expanded to the size of the heading if the heading is larger than the field. In REPORT, headings are truncated to the size of the field.
This command displays a list of the valid commands and options.
This command shows all fields in the current record selected by the SELECT command that are either R (read) or W (write) in the current view (see the VIEW command). The format is one field per line.
If ALL is specified then all selected records are listed with each record separated by a series of dashes.
If only a number n is specified then the nth record in the collection will be dis played. If both numbers n and m are specified, then all records from record n to record m will be listed.
If a field is a date data type in the describe file then SELECT will convert the field into either the YYMMDD or YYYYMMDD format for display, update and comparison.
If the field has a mask in the describe file then it will be used to format the data for display.
If ON is added then the listing is written to the specified list file.
This command is used to lock out the ADD, DELETE and UPDATE commands. Once a command is locked, it cannot by used again until the UNLOCK command has been issued for that command.
This command changes the mask used for the specified field. You cannot specify a mask for a field that is a data type that implies a specific format, such as a date, phone number, social security number, etc. If the mask is invalid for the data type, an error message will be displayed and the mask will be rejected. String masks must have at least one pound sign ("#") to be valid. The initial mask for a field is taken from the describe file.
If no mask is specified then the mask for the given field is deleted.
The OPEN command brings up the original database selection screen and allows you to change the current database or translations.
This command produces a report of the current collection with one line per re cord. If the VIEW parameter is specified, then all fields with either R (read) or W (write) in the current view (see the VIEW command) are printed.
A field list is specified as a set of field names separated by commas. For example PRINT COMPANY, CLIENT, ACCOUNT prints a report of three fields.
If a field is a date data type in the describe file then SELECT will convert the field into either the YYMMDD or YYYYMMDD format for display, update and comparison.
If the field has a mask in the describe file then it will be used to format the data for display.
If the ON option is used then the report is written to the specified list file.
If the QUEUE option is used then the print file will be queued to print. If no queue name is given then the queue name (if any) defined by the SET QUEUE command or initially set up by the symbol DSI_DEFAULT_QUEUE is used.
The FORM option is used to specify a form name and the COPIES option specifies how many copies to print. These two options can only be used if the QUEUE option is used. If no form is specified then the form name (if any) defined by the SET FORM or initially set up by the symbol DSI_DEFAULT_FORM is used.
If the number of copies is not specified, then one copy is printed.
The DO key from the Sel> prompt causes a PRINT VIEW.
This command will cause SELECT to exit.
This command loads a previously saved view as the current view.
This command allows you to display the command recall buffer and select a particular command. If just RECALL is entered then all commands stored in the command buffer are displayed along with an index.
If a number is specified then the command that corresponds to the index given becomes the default for the next command.
If a text string is given then the most recent command that starts with the given text will be recalled. If no command in the buffer matches the text then an error message is displayed.
Previous commands can also be recalled by using the up and down arrows.
This command generates a report of the records selected by the FIND command. See section on REPORT for a full explanation.
This command resets either the COBOL mode or the COUNT mode. See the SET command for a further description of each mode.
This command copies a previously saved collection (see SAVE) to a new collection and makes it the current collection. Only collections that were made from the actual database can be restored, so they cannot be accidentally used with any other database including converted copies of a database.
This command deletes the current pointer file created by the FIND command and then uses the previous pointer file. This allows you to "undo" FIND commands.
This command copies the current collection to the collection name. A saved collection cannot be accessed again until the RESTORE command is issued to make it the current collection again. This command does not affect the current collection.
Note that the internal file ID of the database is combined with the collection name so collection names are specific to the database. Based on the length of the internal file ID, the collection name is limited in length but it can always be at least 22 characters long.
This command allows you to move between records in the current collection for the LIST command.
If you specify NEXT, the next record in the collection becomes the current record. If you issue the command SELECT NEXT and you are on the last record then the message "End of pointers" is displayed and the current record remains unchanged.
If you specify PREV, the previous record in the collection becomes the current record. If you issue the command SELECT PREV and you are on the first record then the message "End of pointers" is displayed and the current record remains unchanged.
If you specify LAST, then the last record in the current collection becomes the current record. To make the first record in the collection the current record, enter "SELECT 1".
If you specify a number, then that record in the current collection becomes the current record. The number specified must be from 1 to the total number of re cords in the collection.
SET COBOL switches on automatic conversion of internal dashes to under scores. This allows fields to be specified as allowed by COBOL programs even though describe only allows underscores, not dashes, in field names.
SET COUNT numbers records displayed by the PRINT VIEW command.
SET FORM sets the form name to be used in a PRINT command when no form is specified.
SET QUEUE sets the queue name to be used in a PRINT command when the qualifier QUEUE is used without a queue name.
SET WILD enables wildcard substitution in FIND commands. See the section "Wild card parameters in FInds" for a description. Entering the SET WILD, without a character turns off wildcard substitution
SHOW STATUS displays the version number of SELECT, the describe file, data base and translation files open, the current COBOL and DEBUG mode, the default queue, default form and the number of records in the current collection.
This command allows you to specify up to three lines of text on the top of each page produced by the PRINT command. The number 1, 2 or 3 tells which line to put the text on. The text is automatically centered on the page.
If only the line number is given then the text for that line will be deleted.
The default text is the database name on the first line with the second and third lines blank.
The TOTAL command sets the total flag in the current view for the specified fields. If a + is given before the field list then the fields specified will be set to total while - removes the total flag from a field. If neither a + or - is used then only the total flag for the fields specified will be set and all other fields will have the total flag reset.
If the ADD, DELETE or UPDATE commands have been locked, either through the lock command or SELECT being started in a production environment, then each command can be unlocked by entering UNLOCK and either ADD, DELETE or UPDATE.
This command first displays all fields set to R (read) or W (write) in the current view (as LIST) and then prompts for the new value of each field that is set to W (write).
If the field being updated changes a key and that key has no changes set, such as the primary key, then the message "A key has been changed, Do you want to up date the record anyway " will be displayed. If you answer N or press RETURN then the field will not be changed. If you enter Y, then the current re cord will be deleted and a new record with the changed key will be added.
When ALL is specified, you will be prompted for the new values for the first re cord and then all the other records in the current collection will be updated with the values entered for the first record.
When EACH is specified you will be shown the values of each field set to read or write and prompted for the new values of each field for each record. This allows different values to be entered into each record.
If a record number n is entered, only that record is updated. If a range of records is entered as, for example, 10 to 20, then SELECT asks for the new values for record 10 and then updates the fields for records 11 to 20 with the values from the first record.
This command does not work in the read-only versions of SELECT.
This command is also initially locked out if you are in a production environment (see section and the UNLOCK command).
The VIEW command has two basic modes. If only the command VIEW is entered, with no other parameters, then SELECT switches to the view full screen mode. Otherwise, if a field list is given, SELECT processes the command and prompts for the next command. (See the section VIEW - LINE COMMAND MODE).
VIEW - FULL SCREEN MODE
In full screen mode, SELECT shows up to the first 20 fields in the describe file, each on a separate line. If there are more than 20 fields in the describe file then you can access the other fields using the Next Screen and Prev Screen keys.
For each field the current view setting, field number, field name, starting position, size, data type and edit mask are displayed.
The view setting is changed by entering a combination of the following letters:
R enables the field for display in the LIST, DUMP and PRINT VIEW commands.
W allows the field to be changed using the UPDATE or ADD command and displayed by the LIST, DUMP and PRINT VIEW commands.
T causes the PRINT command to total this field. This only applies to numeric data types. Setting this also causes the field to be displayed when the LIST, DUMP or PRINT VIEW commands are used.
1-9 Causes the PRINT command to perform a level break when the contents of this field changes. A value of 1 is considered the lowest or most often break, continuing up to level 9.
For example, the setting R1 causes the field to be displayed and the first level break will occur when the contents changes.
The REMOVE key clears all view flags for all fields.
If you are using a DecTerm session from a workstation then the mouse can be used to change the field view. Each of the three buttons toggles a view attribute. Mouse button 1 toggles read (R), button 2 toggles write (W) and button 3 toggles total (T).
If the view setting is blank then the field will not be displayed by the LIST, DUMP or PRINT VIEW commands. The view setting can be blanked by entering a SPACE then RETURN.
You can pick which fields in the database are displayed in the view setup screen by establishing a field name filter. This is done by pressing the SELECT key and then entering a wild card string that is used to match the field names. The wild card character * matches any number of characters while the wildcard character % matches any single character. For example if you enter *PAS* then only field names that contain "PAS" will be displayed. This is extremely useful when the database contains a large number of fields. When the filter is active, the filter string will be displayed a the top right of the screen.
Note that setting a filter only affects which fields are displayed in the view and not the view flags themselves. For example you could set the view to read on several fields, enter a filter, and then set a few more fields to read. The resulting field list in a PRINT VIEW command would be all the fields set to read.
To remove a filter press SELECT and then enter a SPACE and then RETURN to the filter prompt.
The DO key causes SELECT to execute a PRINT VIEW command based on the current view.
Note that clicking on a command on the status bar causes that command to be executed. The PF1 key toggles the mouse off and on to allow the normal DEC Term cut and paste functions.
VIEW - LINE COMMAND MODE
If you use a plus sign, then the specified fields are set to R (read). If you use a minus sign, then the specified fields are set to N (no display). If you use a pound sign (#), then the specified fields are set to W (write). If no plus, minus or pound sign is used, then all fields not specified in the field list are set to N (no display) while all fields in the field list are set to R (read).
The parameter ALL causes all fields to be set either to R (read) or to N (no dis play) depending on whether a plus or minus was specified before the ALL. If no plus or minus is used, then all fields are set to R (read).
A field list is a series of field names in the describe file separated by commas. Each field name may contain either an * or % as wildcard characters.
Examples:
VIEW -ALL All fields are set to N.
VIEW COMPANY, CLIENT The fields COMPANY and CLIENT are set to R, all others are set to N.
VIEW +ACCOUNT The field ACCOUNT is added to the current view (the view flag for the field is set to R.) All other fields are unchanged.
VIEW -COMPANY The field COMPANY is removed from the current view (the view flag is set to N.) All other fields are unchanged.
VIEW + *A%T* All fields with an A, any single character, then a T are added to the current view.
The current view is stored in the file specified. If no view file is given then the database name with an extension of .VIEW is used.
The syntax for the find command is as follows:
FIND [KEY k] [FIRST n] [CURRENT] [] [SORTED BY ]
= [ ]
= { AND | OR }
= [NOT]
= |
This rather formidable definition simply means that you specify each field in the record that you want to select on and then specify some test to say whether or not it has a desired value and then combine the result from each field with AND or OR. For example, you can enter FIND COMPANY = "123" AND ZIP = "92627" to locate all records in the current database that have a company of 123 and a zip of 92627.
If the command FIND is entered without any other qualifiers, then all records in the database are selected.
The key word KEY is used to override the automatic selection of an RMS key for reading the file. This is useful when SELECT would use an alternate key instead of the primary key but the information in the alternate key would still require reading more than 10 to 20 percent of the file. In this instance, it is faster to read the file by its primary key.
The key word FIRST is used to limit the search to a given number of records selected. If you want to find only one occurrence of a field, use FIRST 1 to stop reading the database as soon as any record is selected.
The key word CURRENT causes SELECT to search the current collection for matches to the FIND command, rather than searching the entire database. This is useful in reducing a set of records by specifying further tests or for sorting the current collection of records into a different order. For example, if you have selected 1000 records from a million record database and upon looking at some of the records, you realize that some records should not have been included because they were not for a particular company, then entering FIND CURRENT COMPANY = "XYZ" would not re-read the entire database, but would build a new collection of only those records in the current collection that were for company XYZ.
Note that if you accidentally enter a FIND command either with or without the CUR RENT key word and you get zero records selected, you can always use the REVERT command to go back to the previous collection of records.
Any field in the describe file can be used. If COBOL mode is set, then field names can be entered with internal dashes and SELECT will convert the dashes to underscores. For example LAST-INVOICE-DATE will be converted to LAST_INVOICE_DATE.
Constants can be either strings, dates or numbers. All strings and dates must be en closed in either single or double quotes. Dates are either in YYMMDD format if the date field is 6 bytes long, YYYYMMDD format if the date field is an 8 character string or YYYYMMDDHHMMSSSS format if it is to be used to match a VMS date. The VIEW command will display the data type and length of a field.
The first set of tests can be applied to any field, regardless of data type, as long as the data being tested matches the data type of the field. For example a string field can only be compared to a string constant, not a number.
The field contents are equal to the value given.
The field contents are less than the value given.
The field does not contain the value given.
The field is greater than the value given.
The field is greater than or equal to the value given.
The field is less than or equal to the value given.
The following tests are valid only for string fields.
This allows you to match on fields based on the left part of the field.
For example:
DESC STARTING WITH "ABC"selects all records whose DESC field starts with the letters ABC. This can also be used to select VMS dates. For example if TIME_ADDED is a VMS date field that contains the system time when a record was added then
TIME_ADDED STARTING WITH '20080101'selects all records that were added on January 1, 2008.
The field contains the value given. For example
DESCRIPTION CONTAINING"ABC"is true if the field DESCRIPTION has the letters ABC anywhere in the field.
The field matches the value given considering any wild card characters specified. The wild card character * matches any number of characters while the wild card character % matches any single character.
Examples:
A* Matches any string starting with A
*Z Matches any string ending in Z
*DEF*Matches any string that has the three characters DEF anywhere in it.
A*C%D Matches any string that starts with A, has any number of characters until a C and then has any single character and then ends in a D.
2008%%01*Matches a VMS date field that is any first day of a month in 2008.
The field matches one of a list of values. If the list of values starts with a punctuation mark then that character is used to separate the values in the list. Otherwise, a comma is used to separate the values. The list of values is a maxi mum of 80 characters long.
For example:
FIND COMPANY IN 'AS,AT,BE'
This selects records for companies AS, AT or BE.
FIND FLAG IN '/*/,/'
This selects records whose flag field contains either an asterisk or a comma.
Any comparison may be reversed by using a NOT before the first field name. For example:
FIND NOT COMPANY IN 'AS,AT,BE'
Select all records except company AS, AT or BE.
FIND NOT(ADMIT_CODE = "IN" OR ADMIT_CODE = "OB")
Selects records with admit codes other than IN or OB.
There are times when you have a list of values, usually a key, and you want to build a pointer file that contains each record. If the list is small, this can be accomplished by entering a find command such as:
FIND COMPANY = 'AS' OR COMPANY = 'XX' etc.
or
FIND COMPANY IN "AS,XX,etc."
Both of these FIND commands have limitations that prevent them from being used when the list is large. Also the use of an OR or IN requires SELECT to read the entire database instead of using a key lookup.
The solution is the SET WILD command. Entering "SET WILD *" enables the asterisk as a wild card in the find. When a find contains an asterisk, you will be prompted for a data source to replace the asterisk with, defaulting to SYS$INPUT or your terminal. If the source is a file, then each value must be on a separate line.
If there are more than one wildcard characters in the Find, you will also be prompted for the delimiter in the input data that separates the values for s single record. This delimiter defaults to a comma.
If you selected SYS$INPUT for the data source, you are then prompted for a set of values. There should be the same number of values, separated by the delimiter as there are wildcard characters in the original FIND command. SELECT then substitutes the values into the FIND and executes it. This may result in no records, one record or many record selected. SELECT then prompts for the next set of values and repeats the FIND command however the records selected are now appended to the previous collection instead of starting a new collection as FIND would normally do. After the last set of values has been entered, hit either F10(Exit) or a control-Z and you will be returned to the SEL> prompt.
Note that if the FIND command is not able to use a key to limit its scope, all records in the file will be read for each set of data entered.
If you selected a file as the data source, each line is read and processed as above until the end of file is reached.
For example, suppose you have a list of companies and invoice dates for an invoice file.
Sel> SET WILD *
SEL> FIND COMPANY = "*" AND INVOICE_DATE = "*"
Wildcard source
Delimiter
Enter either F10 or control-Z to finish
Data : AA,960105
Data : BB,960221
.
.
.
Data :
Sel>
The selected records can be sorted by appending SORTED BY to the end of the FIND command and then listing up to 10 fields names to sort on, separated by commas. If you want to have the records sorted in descending order then prefix the field with a minus sign. For example:
FIND COMPANY = '293475' SORTED BY -AMOUNT
This sorts the records for the specified company from high amount to low amount.
You can also sort the current collection of records into a new order by entering FIND CURRENT SORTED BY .
If the FIND command is longer than one line, it can be continued to the next line by breaking it at a point where there is obviously more information needed. SELECT will then prompt "More>" and allow you to continue the FIND command.
For example if the entire FIND command is
FIND COMPANY = '000001' AND ACCOUNT = '123456'
you can enter "FIND COMPANY = '000001' AND" on one line and SELECT will prompt for "More>" and allow you to enter "ACCOUNT = '123456' on the next line. However if you only enter "FIND COMPANY = '000001' then SELECT sees that as a complete command and starts the selection process.
Sometimes, after a FIND command has been entered, you may decide that you don't want to let the FIND complete. This would usually occur when searching a large data base. If you enter a Control-C, the FIND will stop and show how many records have been selected so far. If a sort order was specified, SELECT will prompt you if you want the records selected to be sorted.
Given a describe file that contains the string fields COMPANY, ACCOUNT, INVOICE, INVOICE_DATE, DESCRIPTION and the double field AMOUNT, the following FIND statements are valid:
FIND COMPANY = '122334' AND AMOUNT > 100 SORTED BY DESCRIPTION
This selects records for company '122334' with an amount over 100 sorted by the description.
FIND FIRST 5 AMOUNT NE 0 OR INVOICE_DATE > '20080101'
This finds the first 5 records in the file with an amount not equal to zero or an invoice date after January 1, 2008.
FIND CURRENT SORTED BY DESCRIPTION, DATE, CODE
This resorts the currently selected records into description, date and code order.
FIND DESCRIPTION STARTING WITH "WIDGET"
This finds records whose description field starts with WIDGET.
The REPORT command is used to create a custom report based on a collection of records established by the FIND command. Data may be reported utilizing a variety of functions that include masking, concatenation, conditional display, totaling, level breaks, and page breaks. Fields may be included from up to four translational data bases - where record selection is based on the primary key. Report command files may be saved for modification and/or repeated use.
The user is prompted for report format information - line spacing, column spacing, lines per page, level breaks, and page breaks (syntax discussed below) - as well as print queue and form information. A report may be output to the terminal, a printer, or a text file.
Generating a Report
The Report utility is invoked from the Select prompt with either:
SEL> REPORT
to produce a detail report or
SEL> REPORT SUMMARY
to produce a summary report (one line per first level break). The records in the current collection (last FIND) are used in the report. Note that the data must be sorted into the correct order or else the level break will occur too often.
The following questions will be asked:
Detail line spacing ?
Enter the number of lines between each detail line on the report. Valid responses are SINGLE, DOUBLE, TRIPLE, 1, 2, 3, (Etc...)
Spaces between each column (0-20) ?
Enter the number of spaces between each report field. The minimum is zero and the maximum is 20.
COL#n Pos:m field?
Enter the field name, field number or a constant enclosed in quote marks that you want to be displayed in this column. After all fields have been entered, enter RETURN. The current column number and column position are displayed as part of the prompt.
Due to the number of options that can be specified on one line, the input can be continued over multiple lines by ending each line in a dash. The dash will be dropped from the input and you will receive the prompt 'More> ' and you can continue the line. Note that unlike the FIND command, no processing of a line occurs until a line not ending in a dash is entered.
The number of records within a level break can be used as a field by specifying *COUNT for the field name. If no mask or length is specified then you will be prompted to enter a mask. If you press RETURN to the mask prompt, you will be asked for a field length. The *COUNT displays a 1 for each record, which when totaled, gives the number of records for that level break. Normally this would only be used in a summary report to show how many records were used to create each summary line.
The record number in the current collection can be displayed by specifying *POINTER. Note that adding a /TOTAL to this produces a total that is not very useful. For example, the total of records 10, 11 and 12 is 33.
In addition to a single field, multiple fields can be combined by using the following operations.
&Concatenates the fields or constant, for example if the field
WORK_AREA contains the value "INVENTORY" then entering
"AREA : "&WORK_AREA will produce "AREA : INVENTORY".
+Adds the fields together
-Subtracts the fields
*Multiplies the fields
%Divides the fields. (A "/" could not be used as a "/" starts a switch value.)
SELECT will try to evaluate a string field as a number if one of the numeric operations is used. However if the field contains data that is an invalid number, it is interpreted as 0.
The default action of the report writer can be modified by the following switches appended to the field name:
/COMP[RESS]:
Remove spaces from a string. The action can be EXTRA, LEADING, TRAILING
or ALL. EXTRA removes all leading and trailing spaces and converts all
sets of multiple spaces to a single space, LEADING removes all spaces
in the front of the field, TRAILING removes all spaces from the end and
ALL removes all spaces. For example if a field contains the string "
TEXT WITH MANY SPACES ", printing the field with /COMPRESS:EXTRA will
print "TEXT WITH MANY SPACES".
/DATE
Displays a 6 character date in MMDDYY format or an 8 character date in
MMDDYYYY format. This can then be masked to produce a display of
MM-DD-YY or MM/DD/YYYY for example.
/HE[ADING]:
Replaces the default column heading of the field name with the given
column heading. If no heading is specified then no heading for the
column is generated by this command. Note that the column may already
have
a heading if another field is at the same position because of the /
POS switch.
/IF:
This allows conditional display of a field. The field will be displayed
only if the specified test is true. The allowed operations are:
| = | Equal to |
| < | Less than |
| > | Greater than |
| <> | Not equal to |
| <= | Less than or equal |
| >= | Greater than or equal |
Example:
COL#1 Pos:1 field? "NOT PAID"/IF:DATE_PAID="" COL#2 Pos:9 field? DATE_PAID/IF:DATE_PAID""/POS:1/DATE
Note that by using a mutually exclusive set of tests and a position switch on the second column results in the report showing the text "NOT PAID" if the date paid is blank, otherwise the date paid will be shown.
Field in translated records may be used in the IF by specifying them as (). For example:
AMOUNT_DUE/IF:CLIENT(ACTIVE)="Y"
prints the amount due if the field ACTIVE in the CLIENT database contains a Y.
Currently, only one /IF qualifier may be used on a report line.
/LEN:
Decreases or increases the fields length to 'nn' characters. This can
be used to truncate fields such as names or addresses to make room for
other data on a report.
/MASK:
Displays data in specified mask format. If the mask contains a slash,
then enter a question mark and you will be prompted for the mask on a
separate line. This prevents the slash in the mask from being
interpreted as another switch.
Typically, money amounts are stored as double precision numbers in pennies instead of dollars to prevent accumulated truncation errors. This means $5.26 is actually stored as 526 and must be divided by 100 before the mask is applied as in:
AMOUNT_DUE % "100"/MASK:###,###.##
/POS:
Prints the field at the specified position. If the current line is
already longer than the specified position then a new line will be
started and the field printed at the specified position.
/TOTAL
Prints subtotals and totals for this column
/TR:()
Uses the current field value as a primary key for the translation data
base, whose tran name is specified, and prints the contents of the
specified field.
If the primary key in the translation database has a prefix then you
can use the & operator to append the prefix to the field value. For
example, if a database has a field STATUS with the value "CANC", the
translation database CODE has a key composed of the fields PREFIX and
CODE and the PREFIX for status records is "STAT" then the field
DESCRIPTION in the translation database can be displayed by :
"STAT"&STATUS/TR:CODE(DESCRIPTION)
A special form of the translation is used by entering the field as $DIR /TR:(). This uses the field list as a key and looks up the record in the translation database but does not display anything or update the column position. Once this has been done, a translated field can he specified by the form : (field). This allows translated fields to be used in combination with other fields. For example:
Col 1, Pos1, field? $DIR PART_NUMBER/TR:PARTS() Col 2, Pos 1, field? QUANTITY * PARTS(COST)This multiplies the quantity in the current record by the cost in the PARTS database, using the part number as a key.
Lines per page ?
Enter number of lines for display on each page.
Level breaks (/NOGRAND)?
Enter the number of level breaks used in report. The maximum number of breaks is ten. Be sure the collection is sorted into the right order for the level breaks, otherwise you will get a very long report.
Adding the switch /NOGRAND will cause the final grand total to be suppressed.
For each level break the following questions are asked:
Level 1...field?
Enter field number or field name to break on. The following switches can be used on the break field:
/PAGE
This resets the page number back to 1 at this level break. This can
only be set on one level.
/TEXT
When this switch is entered, you will be prompted for the text to print
at the end of a level break. If the text contains a field name, with
possible switches enclosed in angle brackets "", then the field will be
evaluated using the last record before the level break. For example, to
print a department name at a level break you could use the string
"Total for Department ".
Top of page after level break (Y/N) ? Enter "Y" to start a new page each time this break occurs.
Report date ?
Enter RETURN for current system date. If the text entered does not contain a slash ("/"), then the text will replace the date on the top left of each page and the second header line will show the time the report started. If the text contains a slash, then the left portion up to the slash will replace the date on the first page heading line and the right portion after the slash will replace the time on the second page heading line.
Heading 1, 2 and 3?
Enter the heading to appear on report. Press RETURN for none. Up to 3 headings may be entered. If the text contains a field name, with possible switches enclosed in angle brackets "", the field will be evaluated using the first record on the page. For example to print a department name, use "Department ".
As soon as no data is entered, the rest (if any) heading prompts are skipped.
Number of copies ?
Enter the number of copies to print. The maximum is 10.
Note that only one copy is actually created but the number of copies is used when the report is sent to a print queue. If the report is not submitted to a print queue, the number of copies is ignored.
Report Name ? Enter the name of the file for the report. If no report name is specified, the report will be output to the terminal.
Queue/Form ?
Enter the name of the print queue to send the report to. If you want to specify a form the append the form name to the queue name separated by a slash. For ex ample, to send the report to queue LTA2 with form LASER_LAND, enter LTA4/LASER_LAND. Leaving this field blank by pressing RETURN will not queue the file.
Save file name (/FULL) ?
Enter the name of file where the report commands will be saved. The same re port may be run again by entering @ from the SEL> prompt. Press RETURN to not save the report commands.
The saved report commands can be edited with any editor that produces regular text files, such as EDT or TPU. Do not use a word processing program such as Word Perfect. Note that in the file all text preceding the --> on each line is considered a comment and is not used when the file is read in again. It is therefore unnecessary to update things like the field number if a new field is inserted. If you want to get a clean version of the file after editing it, execute the report file using the @ command and resave it at the end.
Adding the switch /FULL will cause the database setup screen to be saved along with the report commands. This creates a command file that can be executed from the DCL prompt, that opens the databases and creates the report but does not include any FIND commands. These have to be manually edited into the command procedure.
Sample Report Command Files
The following examples are contained in the files DSISAM:REPORT_n.COM. They use the DSI Change Control and Change Codes describe files in DSI_DSC: and the associated databases in DSI_SITE_DATA: All the examples assume the records have been sorted into WORK_AREA and PRIORITY order.
Example 1
The first example selects change records that do not contain a status of DONE and produces a simple report of several fields.
REPORT Detail line spacing ? --> 1 Spaces between each column (0-20) ? --> Field 1 --> WORK_AREA Field 2 --> PRIORITY Field 3 --> CHANGE_NO Field 4 --> DESCRIPTION_1 Field 5 --> PROGRAMMER Field 6 --> Lines per page ? --> 55 Level breaks --> 1 Level 1...field? --> WORK_AREA Top of page after level break (Y/N) ? --> Y Report Date --> Heading 1 --> OPEN CHANGES - STATUS REPORT BY PRIORITY Heading 2 --> EXAMPLE 1 Heading 3 --> Number of copies --> 1 Report Name --> REPORT:EXAMPLE_1.LIS Queue/Form --> EXIT $
Example 1 Listing
05-Apr-95 OPEN CHANGES - STATUS REPORT BY PRIORITY Page 1 06:17 PM EXAMPLE 1 WORK P CHANGE DESCRIPTION PROGRAMMER AREA NO 1 DSI_MODULE 0 000022 ADD INSERT/OVERSTRIKE TO ENTER_STRING JEFFREY DSI_MODULE 0 000019 MODIFY ENTER_STRING TO NOT CALL MACRO MODULES JOHN DSI_MODULE 1 000012 ENTER_FILE.BAS DAVE DSI_MODULE 1 000010 ENTER_PROCEDURE.BAS DAVE
Example 2
Two column headings are changed and the length and position of two fields are modified.
REPORT Detail line spacing ? --> 1 Spaces between each column (0-20) ? --> Field 1 --> WORK_AREA/LEN:10 Field 2 --> PRIORITY/LEN:4/POS:13/HEAD:PRIO Field 3 --> CHANGE_NO Field 4 --> DESCRIPTION_1/HEAD:CHANGE_DESCRIPTION Field 5 --> PROGRAMMER Field 6 --> Lines per page ? --> 55 Level breaks --> 1 Level 1...field? --> WORK_AREA Top of page after level break (Y/N) ? --> Y Report Date --> Heading 1 --> OPEN CHANGES - STATUS REPORT BY PRIORITY Heading 2 --> EXAMPLE 2 Heading 3 --> Number of copies --> 1 Report Name --> REPORT:EXAMPLE_2.LIS Queue/Form -->
Example 2 Listing
05-Apr-95 OPEN CHANGES - STATUS REPORT BY PRIORITY Page 1 06:17 PM EXAMPLE 1 WORK PRIO CHANGE CHANGE PROGRAMMER AREA NO DESCRIPTION
DSI_MODULE 000022 ADD INSERT/OVERSTRIKE TO ENTER_STRING JEFFREY DSI_MODULE 000019 MODIFY ENTER_STRING TO NOT CALL MACRO MODULES JOHN DSI_MODULE 1 000012 ENTER_FILE.BAS DAVE DSI_MODULE 1 000010 ENTER_PROCEDURE.BAS DAVE