QUERY_TABLE Function
Subsets a table created with the BUILD_TABLE function.
Usage
result = QUERY_TABLE(table, '
[Distinct] * | col1 [alias][, ... , coln [alias]]
[Where cond] [Group By colg1[,... colgn]] |
[Order By colo1 [direction][, ..., colon [direction]]] ')
Note that the entire second parameter is a string and must be enclosed in quotes. Also, note that the vertical bar (|) means “or” in this usage. For instance, use either “*” or “coli [alias] [, ..., coln [alias]]”, but not both.
Input Parameters
table—The original table (created with the BUILD_TABLE function) on which the query is performed.
*—An optional wildcard character that includes all columns from the original table in the resulting table.
Distinct—Qualifier that removes duplicate rows from the resulting table.
coli—The list of columns that you want to appear in the resulting table. Use the asterisk (*) wildcard character to select all columns in the original table. The col names can be arguments to the calculation functions used with the Group By clause.
alias—Changes the input table’s column name, coli, to a new name in the output table. If no alias is specified, the input table’s column name is used in the resulting table.
Where cond—A clause containing a conditional expression, cond, that is used to specify the rows to be placed in the resulting table. The expression can contain Boolean and/or relational operators.
Group By colgi—A clause specifying one or more columns by which the rows are grouped in the resulting table. Normally, the grouped rows are data summaries containing results of calculation functions (Sum, Avg, etc.) applied to the columns. (Group By and Order By clauses are mutually exclusive: they cannot be used in the same function call.)
Order By coloi—Name of the column(s) to be sorted (ordered) in the resulting table. The first column named is sorted first. The second column named is sorted within the primary column, and so on. (Group By and Order By clauses are mutually exclusive: they cannot be used in the same function call.)
direction—Either Asc (the default) or Desc. Asc sorts the column in ascending order. Desc sorts the column in descending order. If neither are specified, the column is sorted in ascending order.
Returned Value
result—The resulting table, containing the columns specified by coli, and the rows specified by the query qualifiers and clauses. If the query result is empty, and no syntax or other errors occurred, the result returned is –1.
Keywords
None.
Discussion
Before you can use QUERY_TABLE, you must create a table with the
BUILD_TABLE Function.
A table query always produces a new table containing the query results, or –1 if the query is empty.
Any string or numeric constant used in a QUERY_TABLE call can be passed into the function as a variable parameter. This means that you can use variables for numeric or string values in relational or Boolean expressions. For more information on passing parameters into QUERY_TABLE, see the PV‑WAVE User’s Guide.
note | Within a QUERY_TABLE call, the Group By and Order By clauses are mutually exclusive. That is, you cannot place both Group By and Order By in the same QUERY_TABLE call. |
Boolean and Relational Operators Used in Queries
The Where clause uses Boolean and relational operators to “filter” the rows of the table. You can specify any of the following conditions within a Where clause. Use parentheses to control the order of evaluation, if necessary.
Comparison operators—= (equal to), <> (not equal to),
< (less than), <= (less than or equal to), > (greater than),
>= (greater than or equal to)
Compound search condition—Not, And, Or
Set membership test—In
See the Example section for more information on these operators.
You can also use relational operators (EQ, GE, GT, LE, LT, and NE) in a Where clause instead of the SQL-style operators listed above.
note | When a literal string is used in a comparison, it must be enclosed in quotes—a different set of quotes than those used to delimit the entire QUERY_TABLE parameter string. For more information on using strings in comparisons, see the PV‑WAVE User’s Guide. |
Calculation Functions Used with GROUP BY
The Group By clause is used in conjunction with calculation functions that operate on the values in the specified groupings. Each function takes one column name as its argument. See the Example section for examples showing the use of calculation functions with Group By.
The calculation functions used with Group By are the following, where col is the name of a column:
Avg(
col)—Averages the values that fall within a group.
Count(
col)—Counts the number of occurrences of each data value that falls within a group.
Max(
col)—Returns the maximum value that falls within a group.
Min(
col)—Returns the minimum value that falls within a group.
Sum(
col)—Returns the sum of the values that fall within a group.
Examples
For the following examples, assume table called phone_data contains information on company phone calls. This table contains eight columns of phone information: the date, time, duration of call, caller’s initials, phone extension, cost of call, area code of call, and number of call.
The table used in these examples and the data used to create it are available to you. Enter the following command at the WAVE> prompt to restore the table and data:
(UNIX) RESTORE, !dir+'/data/phone_example.sav'
(WIN) RESTORE, !dir+'\data\phone_example.sav'
For more information on the structure of this table and more examples, see the PV‑WAVE User’s Guide.
For an example showing the use of the Distinct qualifier, see the PV‑WAVE User’s Guide.
The following examples show how to query this table in various ways using QUERY_TABLE.
Example 1
Create a new table, as shown in
Call Data, containing only the phone extensions, area code, and phone number of each call made.
This example demonstrates a simple table query that produces a three-column subset of the original table.
new_table = QUERY_TABLE(phone_data, 'EXT, AREA, NUMBER')
note | For information on printing tables, see the PV‑WAVE User’s Guide. |
Example 2
Show data on the calls that cost more than one dollar.
This example demonstrates how a Where clause is used to produce a subset of the original table, where all rows that contain a cost value of less than one dollar are filtered out.
new_tbl = QUERY_TABLE(phone_data, '* Where COST > 1.0')
Calls Costing more than One Dollar shows an excerpt from the resulting table:
Example 3
Show the total cost and duration of calls made from each phone extension for the period of time the data was collected.
This example demonstrates the use of the Group By clause. The column specified after Group By is the column by which the other specified columns are grouped. The calculation function Sum( ) is used to return the total cost and duration for each extension in the table.
The following command produces this result:
sum_table = QUERY_TABLE(phone_data, $
'EXT, SUM(COST), SUM(DUR) Group By EXT')
This produces the new table, called
sum_table, shown in
Total Cost of Calls, containing the columns
EXT,
SUM_COST, and
SUM_DUR:
note | The cost and duration columns are named in the result table, by default, with the prefix SUM_. This prevents any confusion with the existing table columns that are already named COST and DUR. You can change these default names by including aliases in the QUERY_TABLE function call. |
The INFO command can be used to show the basic structure of this new table:
INFO, /Structure, sum_table
; PV-WAVE prints:
; ** Structure TABLE_2075524386197102262482315, 3 tags, 16 length:
; EXT LONG 0
; SUM_COST FLOAT 0.000000
; SUM_DUR FLOAT 4.49000
The Structure keyword is used because tables are represented as an array of structures. For more information, see the PV‑WAVE User’s Guide.
Example 4
Show the extension, date, and total duration of all calls made from each extension on each date. The results are shown in
Call Extension, Date, and Duration.
This example demonstrates a multiple Group By clause. For example, you can obtain a grouping by extension and by date. The result is a “grouping within a grouping”.
The following command produces the desired result:
tbl = QUERY_TABLE(phone_data, $
'EXT, DATE, Sum(DUR) Group By EXT, DATE')
Note that each multiple grouping produces one summary value. In this case the total duration is calculated for each extension/date grouping. For instance, in the table shown above, the row:
shows the total duration (0.450000) of all calls made from extension 370 on date 901003.
Example 5
Show the number of calls made from each extension for the period of time the data was collected.
This example demonstrates the Group By clause used with the Count function.
cost_sum = QUERY_TABLE(phone_data, $
'EXT, Count(NUMBER) Group By EXT')
The result is a two-column table that contains each extension number and a count value. The count value represents the total number of times each extension number appears in the table. An example is shown in
Table with Extension Number and Count Value.
The parameter specified in the Count function has no real effect on the result, because the function is merely counting the number of data values in the primary column (that is, null values are not ignored). You can obtain the same result with:
cost_sum = QUERY_TABLE(phone_data, $
'EXT, Count(DUR) Group By EXT')
Example 6
Sort the phone_data table by extension, in ascending order.
This example demonstrates how the Order By clause is used to sort a column in a table.
ext_sort = QUERY_TABLE(phone_data, '* Order By EXT')
Call Data Sorted by Extension shows part of the resulting table.
Example 7
Sort the phone_data table by extension, in ascending order, then by cost in descending order.
The table can be further refined by sorting the COST field as well.
cost_sort = QUERY_TABLE(phone_data, '* Order By EXT, COST DESC')
This produces a table organized with the
COST column sorted in descending order within each group of extensions.
Call Data, Sorted by Extension and Cost illustrates the new table organization:
Example 8
The In operator provides another means of filtering data in a table. This operator tests for membership in a set (one-dimensional array) of values. For example, the following array contains a subset of the initials found in the INIT column of the phone_data table:
nameset = ['TAC', 'DLH', 'GWP', 'CCW']
The following QUERY_TABLE call produces a new table that contains information only on the members of nameset:
res = QUERY_TABLE(phone_data, ' * Where INIT In nameset')
See Also
For more information on QUERY_TABLE, see the PV‑WAVE User’s Guide.
Version 2017.1
Copyright © 2019, Rogue Wave Software, Inc. All Rights Reserved.