Data Retrieval Training

1
Data Retrieval Training

• Bridgewater College
• Information Technology Center
• Scott A. Moomaw

Session Two
2
Sentence Format

• (VERB) (FILENAME) (RECORD IDS) (SELECT
  CLAUSES) (SORT CLAUSES) (OUTPUT FIELDS)
  (OPTIONS)
• For example
• LIST B55.DRT WITH LAST.NAME EQ Miller BY
  B55.SORT.NAME B55.NAME

3
Select Clauses

• Multiple SELECT clauses may be listed by
  combining them using AND/OR keywords
• AND is a logical operator that requires multiple
  conditions to all be true
• OR is a logical operator that requires at least
  one of several conditions to be true

4
Implied AND in a SELECT

• Multiple SELECT clauses can be included in a
  sentence without specifying a conjunction
• The WITH clause, when no conjunction is used,
  implies AND

5
Implied AND

• Example
• SELECT B55.DRT WITH B55.AGE EQ 21 WITH GENDER
  EQ F
• This sentence will select all people who are both
  aged 21 and female.

6
Implied OR in a SELECT

• Often you will want to select records using a
  criteria such that we want match multiple values
  for a field
• For instance, we may want to select people who
  are aged 17 or aged 18 or aged 19

7
Implied OR

• Example
• SELECT B55.DRT WITH ZIP EQ 22812 22801
  22843 22401
• This sentence will select records which contain a
  zip code that is one of the listed values

8
Options and Keywords

• Modify the behavior of a sentence
• Some keywords occur at the end of the sentence
  and affect the entire clause
• Some keywords precede a field name and modify
  that element

9
Keyword COL.SPACES

• The COL.SPACES keyword must be followed by a
  number
• It specifies the number of blank spaces to leave
  between columns on a printout
• It is only useful for the LIST verb

10
Keyword COL.SUP

• The COL.SUP keyword causes column headings to be
  ignored on a report
• Only useful for the LIST verb

11
Keyword COUNT.SUP

• Forces a LIST sentence to not include a count at
  the end of the report

12
Keyword DBL.SPC

• Double-spaces a report
• Useful in conjunction with the LIST verb

13
Keywords HEADING/FOOTING

• Used to add report headers and footers
• We will cover this in a future session

14
Keyword ID.SUP

• Suppresses the printing of the records IDs on a
  report
• Useful in conjunction with the LIST verb

15
Keyword LPTR

• Sends the results of this sentence to the default
  printer
• Your printer definition must be setup in advance
  using the SETPTR command which will be included
  later

16
Keyword MARGIN

• Use MARGIN n, to add a margin of n columns at
  the left side of a report
• Can add extra room to a report to accommodate
  binding

17
Keyword NO.SPLIT

• When too many fields are listed on a report,
  there is insufficient room on the page to print
  the columns horizontally
• When this happens, the fields are listed
  vertically
• The NO.SPLIT keyword makes the LIST verb generate
  page breaks so that records do not get split
  across multiple pages

18
Keyword SAMPLE

• Use the syntax, SAMPLE n to choose n records from
  your query for processing
• In effect, the first n records that meet the
  specified criteria are used
• Can be used to test a sentence on a few records
  before processing a large file

19
Keyword TOTAL COUNTER

• Add a counter field to the report
• In reality, the COUNTER is a special field in all
  dictionaries that always contains a value of 1.
  It is simply summed to produce a counter
• The TOTAL COUNTER keyword is really a specific
  form of the TOTAL keyword using the COUNTER field.

20
Keyword VERTICALLY

• Normally reports are printed horizontally unless
  it is not possible
• This keyword forces a report to be vertical even
  when horizontal reporting is feasible

21
Keyword AVERAGE field

• Calculates the AVERAGE of a particular field
• Often used in conjunction with another keyword
  DET.SUP to allow the average to be printed
  without seeing all of the intervening detail

22
Keyword BREAK.ON field

• Causes a breakpoint each time the value of a
  particular field changes
• This allows you to generate reports that are
  grouped such as a printout that contains a new
  page for each residence hall

23
Keyword BREAK.SUP field

• Causes a breakpoint each time the value of a
  particular field changes
• It differs from the BREAK.ON keyword in that the
  BREAK.SUP keyword causes the value of field to
  not be displayed

24
Keyword PERCENT field

• Calculates the percentage of a value for display
  on a report

25
Keyword TOTAL field

• Shows subtotals/totals for a fields value on a
  report
• If the DET.SUP keyword is used, all individual
  records will not be printed
• Using the BREAK.ON keyword, you can generate
  subtotals for a section and a grand total for the
  report

26
Using the SELECT verb
27
SELECT verb

• Allows you to choose a group of records on which
  to work
• Saves the record IDs of the chosen records
• Does not save the contents of the records

28
COLON prompt

• Sentences are typed at the colon prompt
• The colon prompt changes to a greater than sign
  when you have a list of IDs active
• When the prompt is gt instead of , your
  results are limited by what list of record IDs
  are active

29
SELECT plus LIST

• Combining the SELECT and LIST verbs provides
  greater power and flexibility
• You can do several successive SELECT statements
  to accomplish very complex selection
• The final step in the process can be a LIST verb
  to generate a report from the active records

30
Saving a list

• You can store the records IDs that were chosen by
  a SELECT for later use
• The SAVE.LIST command will store the results in a
  file
• The corresponding GET.LIST command retrieves that
  earlier results

31
SAVE.LIST listname

• Will create a file named listname that contains
  the chosen record IDs
• This list can be used immediately or at some
  point in the future
• Useful for historical categorization
• Can also simplify the process of doing multiple
  reports that will all use the same selection
  criteria

32
GET.LIST listname

• Retrieves the list of record IDs that were saved
  by an earlier SAVE.LIST command
• Changes the colon prompt to a greater than sign
  to indicate that a list is active
• Will limit the records that are affected by the
  next sentence

33
DELETE.LIST listname

• Removes an old savedlist from disk
• Frees up room on the system by getting rid of
  old, unwanted lists
• You can see what lists are saved by looking at
  the database for SAVEDLISTS (LIST SAVEDLISTS)
• Each list is stored as a record in that table
  using your name followed by a three digit number

34
CLEARSELECT

• A special verb that cancels an active selection
• Will revert the prompt back to a colon
• Allows the next sentence to act upon an entire
  file as opposed to an active selection

35
SELECT example

• SELECT B55.DRT WITH GENDER EQ F
• SAVE.LIST FEMALES
• GET.LIST FEMALES
• LIST B55.DRT B55.NAME BY B55.SORT.NAME
• GET.LIST FEMALES
• LIST B55.DRT AVERAGE B55.AGE DET.SUP

36
Keyword SELECT.ONLY

• Forces a sentence to work if and only if a
  selection is already active
• This will prevent a sentence from using an entire
  database table simply because an earlier
  selection failed to make a selection active
• Helpful keyword when we start saving sentences
  into canned-jobs for future use

37
Keyword SAVING

• The saving keyword will allow you to save
  information from a field other than saving the
  record ID
• For instance, you can save a list of MAJORs by
  including SAVING MAJORS in your sentence
• The NO.NULLS keywords omits records with an empty
  value for the field being saved

38
Keyword SAVING

• If you anticipate that you may have more than one
  value that is similar when using the SAVING
  keyword, add the clause UNIQUE to your sentence
• SAVING UNIQUE CHILDREN is an example that
  eliminates duplicates
• The SAVING UNIQUE syntax cannot be used in a
  sentence that also contains a SORT clause

39
ExercisesSection Two