The WHERE command uses pre-defined field-identifiers to identify information in the database, an operator to establish a condition under which the information in that field should be extracted, and one or more test field values.
For example, suppose you want your report to include all of the symbols in your project database. To print them, your WHERE command looks like this:
Rules for using the WHERE command
1 Separate the WHERE command field-identifier, operator, and test-field-values with white space.
2 Each WHERE command should have one occurrence of field-identifier and operator for each test-field-value.
3 Separate each text-field-value from the prior one by a comma followed by white space.
4 Enclose in quotation marks any field-identifiers and test-field-values having more than one word.
5 Predefined identifiers used as test-field-values are case-sensitive.
6 The WHERE command sequence is irrelevant when specifying multiple (AND/OR) conditions.
7 Only one of the specified conditions within the OR structure must be met for the data to be included in the extraction database.
8 All of the specified conditions within the AND structure must be met for the data to be included in the extraction database.
9 Multiple or successive WHERE statements indicate an AND condition.
10 Multiple test-field-values in a WHERE statement indicate an OR.
Examples: WHERE command
These are some of the most commonly used field-identifiers in WHERE command statements.
This WHERE statement specifies “include all symbols in the extraction database”.
The Entity Table contains three categories of information called classes. They are Symbol, definition and diagram. Use a similar statement to extract all definitions or all diagrams simply by replacing “symbol” with one of the other database classes.
WHERE Type = "Data Store"
This WHERE statement specifies “include all data stores in the extraction database”.
If this statement is used independently, both data store symbols and data store definitions are included.
If it is preceded by this statement: WHERE Class = Symbol, only data store symbols are included because the first WHERE command statement has specified that all extracted data must be of the symbol class.
WHERE Name != " "
This WHERE statement specifies “do not include blank name fields in the extraction database”.
If this command is used independently, all symbols, definitions, and diagrams that have names are included in a report. If it is preceded by these WHERE statements: WHERE Type = "Data Store" and WHERE Class = Symbol, then only data store symbols that have names are included in the report.
WHERE Relation = uses
This WHERE statement specifies “go to the Relationship Table and trace the ‘uses’ relationship”.
When the Relationfield-identifier is used, the test-field-value must be a valid relationship.
WHERE Description = A
This WHERE statement specifies “include all description properties within the definition dialog that start with the letter A in the in the extraction database”.
Use a full or partial starts with character string option only on text fields. The character string must occur at the beginning of the text field. Enclose the character string in quotation marks if it contains embedded white space.
WHERE "Update Date" > 12/31/92
This WHERE statement specifies “include all entries placed in the encyclopedia after the December 31, 1992 in the extraction database”.
In this example, the predefined field called Update date is used as the field-identifier, and a date is entered as the test-field-value. The date entered must be in the format expected by Microsoft Windows.
WHERE Bottom = T
This WHERE statement specifies “include all symbols that are not represented by child diagrams in the extraction database”.
Derived fields can also be specified as field-identifiers. In this example, the system searches for the derived field called Bottom and, if its value equals “true”, includes it in the extraction database. There are many derived fields that can be used by the Reporting System. Refer to Predefined fields for a complete list.
The following examples show how the text or numeric identifiers could be used in the WHERE command:
This example specifies for the Name and Type to be printed for each line symbol that is unmarked at the beginning of the line (see From association field).
SELECT Name, Type WHERE Class = Symbol WHERE "From Association" = "Unmarked"
This example uses the association number 0, that indicates unmarked. Therefore, the second example produces a listing of all line symbols that are unmarked at the end of the line (To association field).
SELECT Name, Type WHERE Class = Symbol WHERE "To Association Number" = 0
The From Association and To Association fields are dependent on the initial direction in which the line is drawn. Therefore, if the line is drawn from right to left, the values might appear awkward.
Arrows
Process modeling generally uses arrows to display the direction of the data flow. This information can be reported on using the following Boolean database fields, which are specified as being either T or F:
From sequence
A value of T for this field indicates an arrowhead at the beginning of the line.
To sequence
A value of T for this field indicates an arrowhead is present at the end of a line.
Both of these fields are dependent on the initial direction in which the line is drawn. Switching the flow check box from on to off does not change the from and to designation.
Examples: AND conditions in WHERE commands
Example 1
These WHERE statements select from the encyclopedia, diagrams that are Gane & Sarson DFDs AND are level 1 child diagrams.
WHERE Class = Diagram WHERE Type = "Data Flow Gane & Sarson" WHERE Level = 1
Example 2
At the diagram level of the Entity Table, information on all State Transition diagrams in the encyclopedia is placed in the extraction database.
SELECT Name, WHERE Class = Diagram, WHERE Type = "State Transition"
Example 3
These WHERE statements select all the information in the encyclopedia updated during a six month period beginning July 1, 2000 and ending December 31, 2000.
WHERE "Update Date" >= 7/1/2000 WHERE "Update Date" <= 12/31/2000
Example 4
When the WHERE command statements in examples 1 and 2 are used together, only the Gane & Sarson level 1 child diagrams updated between July 1, 2000 and December 31, 2000 are included in the extraction database.
WHERE Class = Diagram WHERE Type = "Data Flow Gane & Sarson" WHERE Level = 1 WHERE "Update Date" >= 7/1/2000 WHERE "Update Date" <= 12/30/2000
