User’s Guide : EViews Fundamentals : EViews Databases : Querying the Database
Querying the Database
Easy Queries
Advanced Queries
Start and End
Last_write and Last_update
Description, Source, Units, Remarks, History, Display_name
Query Examples
A great deal of the power of the database comes from its extensive query capabilities. These capabilities make it easy to locate a particular object, and to perform operations on a set of objects which share similar properties.
The query capabilities of the database can only be used interactively from the database window. There are two ways of performing a query on the database: the easy mode and the advanced mode. Both methods are really just different ways of building up a text query to the database. The easy mode provides a simpler interface for performing the most common types of queries. The advanced mode offers more flexibility at the cost of increased complexity.
Easy Queries
To perform an easy query, first open the database, then click on the EasyQuery button in the toolbar at the top of the database window. The Easy Query dialog will appear containing two text fields and a number of check boxes:
There are two main sections to this dialog: Select and Where. The Select section determines which fields to display for each object that meets the query condition. The Where section allows you to specify conditions that must be met for an object to be returned from the query. An Easy Query allows you to set conditions on the object name, object description, and/or object type.
The two edit fields (name and description) and the set of check boxes (object type) in the Where section provide three filters of objects that are returned from the query to the database. The filters are applied in sequence (using a logical ‘and’ operation) so that objects in the database must meet all of the criteria selected in order to appear in the results window of the query.
The name and description fields are each used to specify a pattern expression that the object must meet in order to satisfy the query. The simplest possible pattern expression consists of a single pattern. A pattern can either be a simple word consisting of alphanumeric characters, or a pattern made up of a combination of alphanumeric characters and the wildcard symbols “?” and “*”, where “?” means to match any one character and “*” means to match zero or more characters. For example:
would successfully match the words production, prediction, and predilection. Frequently used patterns include “s*” for words beginning in “S,” “*s” for words ending in “S,” and “*s*” for words containing “S.” Upper or lower case is not significant when searching for matches.
Matching is done on a word-by-word basis, where at least one word in the text must match the pattern for it to match overall. Since object names in a database consist of only a single word, pattern matching for names consists of simply matching this word.
For descriptions, words are constructed as follows: each word consists of a set of consecutive alphanumeric characters, underlines, dollar signs, or apostrophes. However, the following list words are explicitly ignored: “a,” “an,” “and,” “any,” “are,” “as,” “be,” “between,” “by,” “for,” “from,” “if,” “in,” “is,” “it,” “not,” “must,” “of,” “on,” “or,” “should,” “that,” “the,” “then,” “this,” “to,” “with,” “when,” “where,” “while.” (This is done for reasons of efficiency, and to minimize false matches to patterns from uninteresting words.) The three words “and,” “or,” and “not” are used for logical expressions.
For example:
bal. of p’ment: seas.adj. by X11
is broken into the following words: “bal,” “p’ment,” “seas,” “adj,” and “x11.” The words “of” and “by” are ignored.
A pattern expression can also consist of one or more patterns joined together with the logical operators “and,” “or,” and “not” in a manner similar to that used in evaluating logical expressions in EViews. That is, the keyword and requires that both the surrounding conditions be met, the keyword or requires that either of the surrounding conditions be met, and the keyword not requires that the condition to the right of the operator is not met. For example:
s* and not *s
matches all objects which contain words which begin with, but do not end with, the letter “S”.
More than one operator can be used in an expression, in which case parentheses can be added to determine precedence (the order in which the operators are evaluated). Operators inside parentheses are always evaluated logically prior to operators outside parentheses. Nesting of parentheses is allowed. If there are no parentheses, the precedence of the operators is determined by the following rules: not is always applied first; and is applied second; and or is applied last. For example:
p* or s* and not *s
matches all objects which contain words beginning with P, or all objects which contain words which begin with, but do not end with, the letter S.
The third filter provided in the Easy Query dialog is the ability to filter by object type. Simply select the object types which you would like displayed, using the set of check boxes near the bottom of the dialog.
Advanced Queries
Advanced queries allow considerably more control over both the filtering and the results which are displayed from a query. Because of this flexibility, advanced queries require some understanding of the structure of an EViews database to be used effectively.
Each object in an EViews database is described by a set of fields. Each field is identified by a name. The current list of fields includes:
The name of the object.
The type of the object.
The time this object was last written to the database.
The time this object was last modified by EViews.
The frequency of the data contained in the object.
The date of the first observation contained in the object.
The date of the last observation contained in the object.
The number of data points stored in the series (including missing values).
A brief description of the object.
The source of the object.
The units of the object.
Additional remarks associated with the object.
Recent modifications of the object by EViews.
The EViews display name.
An advanced query allows you to examine the contents of any of these fields, and to select objects from the database by placing conditions on these fields. An advanced query can be performed by opening the database window, then clicking on the button marked Query in the toolbar at the top of the window. The Advanced Query dialog is displayed.
The first edit field labeled Select: is used to specify a list of all the fields that you would like displayed in the query results. Input into this text box consists of a series of field names separated by commas. Note that the name and type fields are always fetched automatically.
The ordering of display of the results of a query is determined by the Order By edit field. Any field name can be entered into this box, though some fields are likely to be more useful than others. The description field, for example, does not provide a useful ordering of the objects. The Order By field can be useful for grouping together objects with the same value of a particular field. For example, ordering by type is an effective way to group together the results so that objects of the same type are placed together in the database window. The Ascending and Descending buttons can be used to reverse the ordering of the objects. For example, to see objects listed from those most recently written in the database to those least recently written, one could simply sort by the field last_write in Descending order.
The Where edit field is the most complicated part of the query. Input consists of a logical expression built up from conditions on the fields of the database. The simplest expression is an operator applied to a single field of the database. For example, to search for all series which are of monthly or higher frequencies (where higher frequency means containing more observations per time interval), the appropriate expression is:
freq >= monthly
Field expressions can also be combined with the logical operators and, or and not with precedence following the same rules as those described above in the section on easy queries. For example, to query for all series of monthly or higher frequencies which begin before 1950, we could enter the expression:
freq >= monthly and start < 1950
Each field has its own rules as to the operators and constants which can be used with the field.
The name field supports the operators “<“, “<=”, “>”, “>=”, “=”, and “<>” to perform typical comparisons on the name string using alphabetical ordering. For example,
name >= c and name < m
will match all objects with names beginning with letters from C to L. The name field also supports the operator “matches”. This is the operator which is used for filtering the name field in the easy query and is documented extensively in the previous section. Note that if matches is used with an expression involving more than one word, the expression must be contained in quotation marks. For example,
name matches "x* or y*" and freq = quarterly
is a valid query, while
name matches x* or y* and freq = quarterly
is a syntax error because the part of the expression that is related to the matches operator is ambiguous.
The type field can be compared to the standard EViews object types using the “=” operator and the keywords: alpha, coef, equation, factor, graph, group, logl, matrix, model, pool, sample, scalar, series, spool, sspace, string, svector, sym, system, table, text, var, vector. Relational operators are defined for the type field, although there is no particular logic to the ordering. The ordering can be used, however, to group together objects of similar types in the Order By field.
The frequency field has one of the following values:
2y, 3y, 4y, 5y, 6y, 7y, 8y, 9y, 10y, 20y
Multi-year in intervals of: 2, 3, 4, 5, 6, 7, 8, 9, 10, or 20 years, respectively.
Ten-day (daily in intervals of ten)
5 day daily, Monday through Friday
7 day daily
Any word beginning with the letter above is taken to denote that particular frequency, so that monthly can either be written as “m” or “monthly”. Ordering over frequencies is defined so that a frequency with more observations per time interval is considered “greater” than a series with fewer observations per time interval. The operators “<”, “>”, “<=”, “>=”, “=”, “<>” are all defined according to these rules. For example,
freq <= quarterly
will match objects whose frequencies are quarterly, semiannual, annual or undated.
Start and End
Start and end dates use the following representation. A date from an annual series is written as an unadorned year number such as “1980”. A date from a semiannual series is written as a year number followed by an “S” followed by the six month period, for example “1980S2”. The same pattern is followed for quarterly and monthly data using the letters “Q” and “M” between the year and period number. Weekly, 5-day daily, and 7-day daily data are denoted by a date in the format:
where m denotes a month digit, d denotes a day digit, and y denotes a year digit.
Operators on dates are defined in accordance with calendar ordering where an earlier date is less than a later date. Where a number of days are contained in a period, such as for monthly or quarterly data, an observation is ordered according to the first day of the period. For example:
start <= 1950
will include dates whose attributed day is the first of January 1950, but will not include dates which are associated with other days in 1950, such as the second, third, or fourth quarter of 1950. However, the expression:
start < 1951
would include all intermediate quarters of 1950.
Last_write and Last_update
As stated above, last_write refers to the time the object was written to disk, while last_update refers to the time the object was last modified inside EViews. For example, if a new series was generated in a workfile, then stored in a database at some later time, last_write would contain the time that the store command was executed, while last_update would contain the time the new series was generated. Both of these fields contain date and time information which is displayed in the format:
mm/dd/yyyy hh:mm
where m represents a month digit, d represents a day digit, y represents a year digit, h represents an hour digit and m represents a minute digit.
The comparison operators are defined on the time fields so that earlier dates and times are considered less than later dates and times. A typical comparison has the form:
last_write >= mm/dd/yyyy
A day constant always refers to twelve o’clock midnight at the beginning of that day. There is no way to specify a particular time during the day.
Description, Source, Units, Remarks, History, Display_name
These fields contain the label information associated with each object (which can be edited using the Label view of the object in the workfile). Only one operator is available on these fields, the matches operator, which behaves exactly the same as the description field in the section on easy queries.
Query Examples
Suppose you are looking for data related to gasoline consumption and gasoline prices in the database named DRIBASIC. First open the database: click File/Open, select Files of type: Database.edb and locate the database. From the database window, click Query and fill in the Advanced Query dialog as follows:
Select: name, type, freq, description
Where: description matches gasoline
If there are any matches, the results are displayed in the database window similar to the following:
To view the contents of all fields of an item, double click on its name. EViews will open an Object Description window that looks as follows:
To further restrict your search to series with at least quarterly frequency and to display the start and end dates of the results, click Query and again and modify the fields as follows:
Select: name, type, start, end, description
Where: description matches gasoline and freq>=q
If you are interested in seasonally adjusted series, which happen to contain sa or saar in their description in this database, further modify the fields to
Select: name, type, start, end, description
Where: description matches "gasoline and (sa or saar)" and freq>=q
The display of the query results now looks as follows:
The columns of the database display can be reordered by dragging each to its desired location, or resized by dragging the column header’s right edge. You can also sort by any of the columns by clicking the column header to alternate between ascending and descending sort.
By adding columns, the database display can be customized to provide additional information and grouping capabilities. See “Workfile Details Display” for a thorough discussion of customized attributes in this view. Most importantly, it describes how to view all available attributes in the database, by right-clicking in a column header and selecting Edit Columns.... The methods presented in the discussion detail how you can view specific characteristics across all items in your database.