Computers: Universe: Listing RecordsComputers: Universe: Listing RecordsTo illustrate the query language, we need a file to enquire about. The examples in this section will use COPY.OF.VOC, which we created to illustrate the use of indexes (see Indexing Fields).
If you did not follow the examples in that section, you can create this file as follows:
>CREATE.FILE COPY.OF.VOC DYNAMIC
Creating file "COPY.OF.VOC" as Type 30.
Creating file "D_COPY.OF.VOC" as Type 3, Modulo 1, Separation 2.
Added "@ID", the default record for RetrieVe, to "D_COPY.OF.VOC".
>COPY FROM VOC TO COPY.OF.VOC ALL
825 records copied.
>COPY FROM DICT VOC TO DICT COPY.OF.VOC ALL OVERWRITING
19 records copied.
>
You also learnt earlier (see Phrases) that the @ phrase is used as to provide 'default' parameters to LIST instructions if they are entered without parameters. In this section, though, this would complicate the examples. We must remove the @ phrase from the dictionary of COPY.OF.VOC to avoid this. The DELETE command used below is straightforward, and explained fully later during our discussion of the TCL command language. For now, enter:
>DELETE DICT COPY.OF.VOC @
1 records DELETEd.
>
Your file is now ready to enquire on.
As you have already discovered, to list the records in a file you need only enter the command LIST followed by a file name. For instance:
>LIST COPY.OF.VOC
LIST COPY.OF.VOC 10:05:46 08-19-99 PAGE 1 VOC......... F9 PRINT.ADMIN TEST.PARAGRA PH COMO NOHEAD LT BREAK.VERB SUBO ALL.MATCH INQUIRING LIST.ME IN MAP.VERB CONSYS.REPOR TS MAKE.DEMO.FI LES STATS PTIME Press any key to continue...
There are 800 or so records in this file, so LIST will pause at the bottom of every page. You may see the next page by pressing ENTER, or abandon the enquiry by pressing Q. If you want the enquiry to run to its last page (perhaps in order to see totals) without pausing after every page, press N. For now, press Q.
This listing displays only record keys. To display other fields, simply add their names to the command:
>LIST COPY.OF.VOC TYPE DESC SIZE
LIST COPY.OF.VOC TYPE DESC SIZE 10:32:37 08-19-99 PAGE 1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
PRINT.ADMIN PA Paragraph 25
TEST.PARAGRA PA PA 64
PH
COMO V Verb - Control copying of 31
command output to files
NOHEAD K Keyword - Do not print a 5
banner page
LT K Keyword - "Less Than" operator 3
BREAK.VERB V Verb - Enable or disable INTR, 12
STOP, SUSP, and BRK keys.
SUBO V Verb - Subtract OCTAL numbers 17
ALL.MATCH K Keyword - Specify that ALL 5
strings should match for
SEARCH/ESEARCH statement
INQUIRING K Keyword - Prompt for record 4
name
LIST.ME S Sentence - Return status of 11
all users with your login name
Press any key to continue...
The order in which these records appear is not, strictly speaking, random, as it is reflects the order in which the records are found in the physical groups, which in turn depends on the hashing of the file (see UniVerse Files). It is not though, as some believe, the order in which the records were added: in fact it has no logical meaning.
To sort your records by fieldname, add the words BY fieldname to the end of your enquiry. To sort first by fieldname1 and within that by fieldname2, add the words BY fieldname1 BY fieldname 2. You can in fact chain as many sorts in this way as you wish. This example sorts first by type, and then by key:
>LIST COPY.OF.VOC TYPE DESC SIZE BY TYPE
LIST COPY.OF.VOC TYPE SIZE DESC BY TYPE BY @ID 10:42:31 08-19-99 PAGE 1
VOC......... TYPE Length... DESC..........................
F1 D 11 Data descriptor for Field 1 of
any file
F10 D 12 Data descriptor for Field 10
of any file
F2 D 11 Data descriptor for Field 2 of
any file
F3 D 11 Data descriptor for Field 3 of
any file
F4 D 11 Data descriptor for Field 4 of
any file
F5 D 11 Data descriptor for Field 5 of
any file
F6 D 11 Data descriptor for Field 6 of
any file
F7 D 11 Data descriptor for Field 7 of
any file
F8 D 11 Data descriptor for Field 8 of
any file
F9 D 11 Data descriptor for Field 9 of
any file
Press any key to continue...
You can sort your records 'backwards' by using BY.DSND ('by descending') in place of BY.
An interesting footnote is that UniVerse provides a SORT command which is identical to LIST in every respect save one: it acts as if BY @ID had been added to the end of the list of BY clauses. There is, perhaps, little harm in it, but I generally recommend that people do not use it, but use instead LIST and add the required BY @ID. This is simply because the latter is more descriptive and less easily misunderstood. For instance, if you saw the command...
SORT COPY.OF.VOC BY TYPE
...would it be obvious to you that the file was being sorted by both TYPE and @ID? The functionally identical...
LIST COPY.OF.VOC BY TYPE BY @ID
...makes this inescapable.
It is sometimes useful to enquire on a sample of the records in your file. Just use the SAMPLE n keyword to list a sample of n records. The records are the first n encountered in the physical order they are stored in the file. As explained above, this is not strictly random, but usually might as well be. Be aware, though, that repeated SAMPLE enquiries on the same file will not yield different sets of records each time.
>LIST COPY.OF.VOC TYPE DESC SIZE SAMPLE 3
LIST COPY.OF.VOC TYPE DESC SIZE SAMPLE 3 11:57:30 08-19-99 PAGE 1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
PRINT.ADMIN PA Paragraph 25
TEST.PARAGRA PA PA 64
PH
Sample of 3 records listed.
>
If you wish to enquire on only a subset of the records in your file, you can use the keyword WITH followed by your criteria. For instance, if you were interested only in seeing M types, you would enter the command:
>LIST COPY.OF.VOC TYPE DESC SIZE WITH TYPE = 'M'
LIST COPY.OF.VOC TYPE DESC SIZE WITH TYPE = "M" 12:00:46 08-19-99 PAGE 1
VOC......... TYPE DESC.......................... Length...
CONV.ACCT.ME M Menu - UniVerse Account 34
NU Importation
PRINT.ADMIN. M Menu - UniVerse Spooler 35
MENU Administration
BASIC.CONT M Menu 31
SP.JOB M Menu 27
CONSYS.MENU M M 26
SP.MOD M Menu 27
ACCT.CONV M Menu 30
SP.STATUS M Menu 30
MENUS M MENU selector - Invoke the 31
MENU maintenance menu
9 records listed.
>
The equal sign = is probably the most commonly used, but still only one of many comparison operators you can use in your criteria. Here is a list of the most popular, with examples:
Operator |
Example and Meaning |
Synonyms |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
You can reverse any of these criteria using NOT: but you don't normally need to, as most of the operators have opposites in any case. For instance, NOT TYPE = 'M' means the same as TYPE # 'M',
More usefully, you can combine these criteria using the keywords AND and OR. Joining two conditions with AND means that they must both be true for the record to be selected. Joining them with OR means that at least one of them must be true.
UniVerse always evaluates AND links before OR links: you need to be careful when combining conditions to ensure that they mean to UniVerse what they mean to you. For instance, consider the following criteria:
...WITH SIZE GT 100 AND TYPE = 'PA' OR TYPE = 'S'
What this criteria will do is select all the paragraphs more than 100 bytes in length, and all the sentences regardless of their lengths. Is this rather odd result what the developer wanted? Probably not: it would seem more likely he was looking for long sentences and paragraphs: ie. any sentence or paragraph more than 100 bytes long. He could have resolved the ambiguity with brackets:
...WITH SIZE GT 100 AND (TYPE = 'PA' OR TYPE = 'S')
The meaning of this is obvious to both developer and UniVerse.
Even if the original developer had intended, for some reason, to choose long paragraphs and all sentences, he still wrote poor code as it is difficult for maintenance programmers to tell whether that was the intended result, or whether the condition contains a bug. He would have been better to include the brackets for clarity, even though they are redundant:
...WITH (SIZE GT 100 AND TYPE = 'PA') OR TYPE = 'S'
No question here what is intended.
To total a numeric column, add the word TOTAL before the appropriate field.
>LIST COPY.OF.VOC TYPE DESC TOTAL SIZE
LIST COPY.OF.VOC TYPE DESC TOTAL SIZE 10:46:25 08-19-99 PAGE 1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
PRINT.ADMIN PA Paragraph 25
TEST.PARAGRA PA PA 64
PH
COMO V Verb - Control copying of 31
command output to files
NOHEAD K Keyword - Do not print a 5
banner page
LT K Keyword - "Less Than" operator 3
BREAK.VERB V Verb - Enable or disable INTR, 12
STOP, SUSP, and BRK keys.
SUBO V Verb - Subtract OCTAL numbers 17
ALL.MATCH K Keyword - Specify that ALL 5
strings should match for
SEARCH/ESEARCH statement
INQUIRING K Keyword - Prompt for record 4
name
LIST.ME S Sentence - Return status of 11
all users with your login name
Press any key to continue...
...to see your total, you'll need to get to the end of the enquiry. Press N at the 'end of page' prompt to skip all further pauses. The enquiry will flash all the pages past your screen until it gets to...
LIST COPY.OF.VOC TYPE DESC TOTAL SIZE 10:46:25 08-19-99 PAGE 88
VOC......... TYPE DESC.......................... Length...
LORRY for packing and
unpacking Universe files into
ASCII 'lorry' files
P.DET V Verb - UNASSIGN a physical 27
PRINTER from your task
LIST.ITEM V Verb - List complete records 37
in specified file
INFORM K Keyword - Display the spooler 5
job number of newly queued
jobs
BREAK.ON K Keyword - Specify BREAK 4
conditions in a report
LISTPA R Remote - LIST the PARAGRAPHS 24
stored in the VOCABULARY file
CUSTOMERS F F 24
=========
21,211
824 records listed.
>
To break the report into sections, add the word BREAK.ON before the appropriate field. This will force a break whenever the field in question changes. Note that this will produce meaningless results unless the records are sorted by the field being broken on, so a sort BY the appropriate field almost always accompanies a BREAK.ON. Note also that totalled numeric fields automatically show subtotals at breakpoints:
>LIST COPY.OF.VOC BREAK.ON TYPE DESC TOTAL SIZE BY TYPE
LIST COPY.OF.VOC BREAK.ON TYPE DESC TOTAL SIZE BY TYPE 10:55:11 08-19-99 PAGE1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
F7 D Data descriptor for Field 7 of 11
any file
F1 D Data descriptor for Field 1 of 11
any file
F2 D Data descriptor for Field 2 of 11
any file
F4 D Data descriptor for Field 4 of 11
any file
F5 D Data descriptor for Field 5 of 11
any file
F6 D Data descriptor for Field 6 of 11
any file
F10 D Data descriptor for Field 10 12
of any file
F8 D Data descriptor for Field 8 of 11
any file
F3 D Data descriptor for Field 3 of 11
any file
** ---------
D 111
MANNY.BP.O F F 26
DICT.DICT F File - The DICTIONARY for all 51
uniVerse dictionaries in the
system
TEST1 F F 16
CUSTOMER.INV F F 30
OICES
DICT.PICK F File - The DICTIONARY for all 51
Pick style uniVerse
dictionaries in the system
CONSYS.RESIZ F F 72
E.FILES
THISISALONGN F F 30
AME
INTEOD.UFD F F 28
Press any key to continue...
(I've cheated here, for clarity omitting the page end prompt between the first and second pages of the report. Depending on the exact contents of your VOC, and thus your COPY.OF.VOC, the first breakpoint will probably occur somewhere after the first page.)
As you can see, RetrieVe puts a line of stars at the end of each section. If you'd like some other visual marker used instead, specify it in double quotes after your BREAK.ON:
>LIST COPY.OF.VOC BREAK.ON "::::" TYPE DESC TOTAL SIZE BY TYPE
LIST COPY.OF.VOC BREAK.ON "::::" TYPE DESC TOTAL SIZE BY TYPE 11:20:57 08-19-99
PAGE 1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
F7 D Data descriptor for Field 7 of 11
any file
F1 D Data descriptor for Field 1 of 11
any file
F2 D Data descriptor for Field 2 of 11
any file
F4 D Data descriptor for Field 4 of 11
any file
F5 D Data descriptor for Field 5 of 11
any file
F6 D Data descriptor for Field 6 of 11
any file
F10 D Data descriptor for Field 10 12
of any file
F8 D Data descriptor for Field 8 of 11
any file
F3 D Data descriptor for Field 3 of 11
any file
:::: ---------
D 111
MANNY.BP.O F F 26
DICT.DICT F File - The DICTIONARY for all 51
uniVerse dictionaries in the
system
TEST1 F F 16
CUSTOMER.INV F F 30
OICES
DICT.PICK F File - The DICTIONARY for all 51
Pick style uniVerse
dictionaries in the system
CONSYS.RESIZ F F 72
E.FILES
THISISALONGN F F 30
AME
INTEOD.UFD F F 28
Press any key to continue...
By including certain special codes in your marker string, you can further control how your breakpoints work. For instance, the 'N' token resets the page number to one after each breakpoint. All the codes take this same form: a single letter in single quotes. To use it, you'd enter the command LIST COPY.OF.VOC BREAK.ON "::::'N'" TYPE TOTAL SIZE BY TYPE.
Other tokens include 'D', which suppresses the breakpoint line if there's only one corresponding record (because who needs to total one record?), and 'P' begins a new page for each breakpoint value. Check out BREAK.ON in the UniVerse documentation for a complete list: but these are the ones I tend to use most often.
If you are only interested in the break points themselves, and not the individual records, you can summarise your report by adding the word DET.SUP (short for 'detail suppress'). Note that this has the effect of hiding the @ID column as this is clearing particular to individual records, and not breakpointed groups. The DESC field would be equally meaningless in a report of this kind and I have omitted it:
>LIST COPY.OF.VOC BREAK.ON TYPE TOTAL SIZE BY TYPE DET.SUP
LIST COPY.OF.VOC BREAK.ON TYPE TOTAL SIZE BY TYPE DET.SUP 11:11:09 08-19-99 PA
GE 1
TYPE Length...
D 111
F 4,351
K 1,783
M 271
PA 4,032
PH 393
PQ 226
Q 117
R 688
S 569
V 8,607
X 63
=========
21,211
824 records listed.
>
So far, the text of the LIST command itself has appeared as the heading of your enquiries. To apply your own heading, add HEADING "heading text":
>LIST COPY.OF.VOC TYPE DESC SIZE HEADING "My COPY.OF.VOC report"
My COPY.OF.VOC Report
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
PRINT.ADMIN PA Paragraph 25
TEST.PARAGRA PA PA 64
PH
COMO V Verb - Control copying of 31
command output to files
NOHEAD K Keyword - Do not print a 5
banner page
LT K Keyword - "Less Than" operator 3
BREAK.VERB V Verb - Enable or disable INTR, 12
STOP, SUSP, and BRK keys.
SUBO V Verb - Subtract OCTAL numbers 17
ALL.MATCH K Keyword - Specify that ALL 5
strings should match for
SEARCH/ESEARCH statement
INQUIRING K Keyword - Prompt for record 4
name
LIST.ME S Sentence - Return status of 11
all users with your login name
Press any key to continue...
You can add footers similarly by adding FOOTING "footer text".
There are a number of special 'tokens' you can include in headers and footers. They all appear between single quotes:
'P' will include a page number.
'D' will include the date.
'L' will include a line break.
'G' will expand to a string of spaces as long as any other 'G' on the same line, and sufficient to make the complete line cover the width of the page. This ludicrous explanation is entirely correct and completely impossible to understand: the net effect is to 'spread out' elements of a heading, so HEADING "'D''G'My Heading'G''P'" will put the date on the extreme left, the page number on the extreme right, and the words My Heading equidistant between them.
>LIST COPY.OF.VOC TYPE DESC SIZE HEADING "'D''G'COPY.OF.VOC Report'G''P'"
08-19-99 COPY.OF.VOC Report 1
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
PRINT.ADMIN PA Paragraph 25
TEST.PARAGRA PA PA 64
PH
COMO V Verb - Control copying of 31
command output to files
NOHEAD K Keyword - Do not print a 5
banner page
LT K Keyword - "Less Than" operator 3
BREAK.VERB V Verb - Enable or disable INTR, 12
STOP, SUSP, and BRK keys.
SUBO V Verb - Subtract OCTAL numbers 17
ALL.MATCH K Keyword - Specify that ALL 5
strings should match for
SEARCH/ESEARCH statement
INQUIRING K Keyword - Prompt for record 4
name
LIST.ME S Sentence - Return status of 11
all users with your login name
Press any key to continue...
You can also link your heading to your breakpoints, such that each breakpoint value starts on a new page, and the breakpoint value itself appears in the heading for that page. To do this, you need to use one of BREAK.ON's special codes (see above) which I've not yet mentioned: 'B'. This causes the breakpoint value to appear in the heading. You also need to put a 'B' token into your heading, telling LIST where to put the value.
This is one of those things that is harder to describe than to do. Check over the BREAK.ON notes again, try the following example, and you should get it:
LIST COPY.OF.VOC BREAK.ON "'B'" TYPE DESC TOTAL SIZE BY TYPE HEADING "Type 'B' records"
Type D records
VOC......... TYPE DESC.......................... Length...
F9 D Data descriptor for Field 9 of 11
any file
F7 D Data descriptor for Field 7 of 11
any file
F1 D Data descriptor for Field 1 of 11
any file
F2 D Data descriptor for Field 2 of 11
any file
F4 D Data descriptor for Field 4 of 11
any file
F5 D Data descriptor for Field 5 of 11
any file
F6 D Data descriptor for Field 6 of 11
any file
F10 D Data descriptor for Field 10 12
of any file
F8 D Data descriptor for Field 8 of 11
any file
F3 D Data descriptor for Field 3 of 11
any file
Press any key to continue...
It's redundant, of course, to show the 'D' type in the heading and then again on ever row. To avoid it, use BREAK.SUP (break on suppressing column) instead of BREAK.ON:
LIST COPY.OF.VOC BREAK.ON "'B'" TYPE DESC TOTAL SIZE BY TYPE HEADING "Type 'B' records"
Type D records
VOC......... DESC.......................... Length...
F9 Data descriptor for Field 9 of 11
any file
F7 Data descriptor for Field 7 of 11
any file
F1 Data descriptor for Field 1 of 11
any file
F2 Data descriptor for Field 2 of 11
any file
F4 Data descriptor for Field 4 of 11
any file
F5 Data descriptor for Field 5 of 11
any file
F6 Data descriptor for Field 6 of 11
any file
F10 Data descriptor for Field 10 12
of any file
F8 Data descriptor for Field 8 of 11
any file
F3 Data descriptor for Field 3 of 11
any file
Press any key to continue...
To send your enquiry results to the printer instead of the screen, add LPTR ('line printer') to your command, as in LIST COPY.OF.VOC TYPE SIZE DESC LPTR. This will print to your 'default printer': which printer is your default printer will depend on how your system adminstrator has configured your system. Go and hassle him if you're in doubt.
If your system has many printers, you administrator may have set up more than one UniVerse 'print channel'. Different channels can route output to different printers or even to files on your disk. They are created with the SETPTR, which we will encounter later in these notes, and each one is numbered. If you need to print to a channel other than the default (channel 0), add the channel number to the end of your LPTR command, as in LIST COPY.OF.VOC TYPE SIZE DESC LPTR 2.