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.
aliasChanges 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 Table 14-1: 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')
 
Call Data
Ext
Area
Number
311
215
2154934242
358
303
2583869
320
214
2142319893
289
303
2955836
248
617
6174941999
332
614
6144695553
0
303
480344
418
303
7725190
379
212
2123056618
370
212
2124157956
0
303
480320
379
818
8185012880
331
512
5125331228
370
303
4441245
0
303
480320
 
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')
Table 14-2: Calls Costing more than One Dollar shows an excerpt from the resulting table:
 
Calls Costing more than One Dollar
DATE
TIME
DUR
INIT
EXT
COST
AREA
NUMBER
901002
093200
21.40
TAC
311
5.78
215
2155554242
901002
094700
17.44
EBH
320
4.71
214
2145559893
901004
095000
3.77
DJC
331
1.02
512
5125551228
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 Table 14-3: Total Cost of Calls, containing the columns EXT, SUM_COST, and SUM_DUR:
 
Total Cost of Calls
EXT
SUM_COST
SUM_DUR
0
0.00000
4.49000
248
0.350000
1.31000
289
0.00000
16.2300
311
5.78000
21.4000
320
4.71000
17.4400
331
1.02000
3.77000
332
0.680000
2.53000
358
0.00000
1.05000
370
0.120000
0.610000
379
0.930000
3.46000
418
0.270000
0.350000
 
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 Table 14-4: 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')
 
Call Extension, Date, and Duration
EXT
DATE
SUM_DUR
0
901003
2.33000
0
901004
2.16000
248
901002
1.31000
289
901002
16.2300
311
901002
21.4000
320
901002
17.4400
331
901004
3.77000
332
901003
2.53000
358
901002
1.05000
370
901003
0.450000
370
901004
0.160000
379
901003
1.53000
379
901004
1.93000
418
901003
0.350000
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:
 
370
901003
0.450000
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 14-5: Table with Extension Number and Count Value.
 
Table with Extension Number and Count Value
EXT
COUNT_NUMBER
0
3
248
1
289
1
311
1
320
1
331
1
332
1
358
1
370
2
379
2
418
1
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')
Table 14-6: Call Data Sorted by Extension shows part of the resulting table.
 
Call Data Sorted by Extension
DATE
TIME
DUR
INIT
EXT
COST
AREA
NUMBER
901003
91600
2.33000
JAT
0
0.00000
303
480344
901004
95300
1.36000
JAT
0
0.00000
303
480320
901004
94700
0.800000
JAT
0
0.00000
303
480320
901002
94800
1.31000
RLD
248
0.350000
617
6174941999
901002
94800
16.2300
TDW
289
0.00000
303
2955836
901002
93200
21.4000
TAC
311
5.78000
215
2154934242
901002
94700
17.4400
EBH
320
4.71000
214
2142319893
901004
95000
3.77000
DJC
331
1.02000
512
5125331228
901003
91500
2.53000
DLH
332
0.680000
614
6144695553
901002
94700
1.05000
BWD
358
0.00000
303
2583869
901003
91600
0.450000
MLK
370
0.120000
212
2124157956
901004
95100
0.160000
GWP
370
0.00000
303
4441245
901003
91600
1.53000
SRB
379
0.410000
212
2123056618
901004
94900
1.93000
SRB
379
0.520000
818
8185012880
901003
91600
0.350000
CCW
418
0.270000
303
7725190
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. Table 14-7: Call Data, Sorted by Extension and Cost illustrates the new table organization:
 
Call Data, Sorted by Extension and Cost
DATE
TIME
DUR
INIT
EXT
COST
AREA
NUMBER
901003
91600
2.33000
JAT
0
0.00000
303
480344
901004
94700
0.800000
JAT
0
0.00000
303
480320
901004
95300
1.36000
JAT
0
0.00000
303
480320
901002
94800
1.31000
RLD
248
0.350000
617
6174941999
901002
94800
16.2300
TDW
289
0.00000
303
2955836
901002
93200
21.4000
TAC
311
5.78000
215
2154934242
901002
94700
17.4400
EBH
320
4.71000
214
2142319893
901004
95000
3.77000
DJC
331
1.02000
512
5125331228
901003
91500
2.53000
DLH
332
0.680000
614
6144695553
901002
94700
1.05000
BWD
358
0.00000
303
2583869
901003
91600
0.450000
MLK
370
0.120000
212
2124157956
901004
95100
0.160000
GWP
370
0.00000
303
4441245
901004
94900
1.93000
SRB
379
0.520000
818
8185012880
901003
91600
1.53000
SRB
379
0.410000
212
2123056618
901003
91600
0.350000
CCW
418
0.270000
303
7725190
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.