QUERY_TABLE Function

Subsets a table created with the BUILD_TABLE function.

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 quotesa 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'

: (OpenVMS)RESTORE, !dir+'[DATA]PHONE_EXAMPLE.SAV'

: (Windows)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 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')

A portion of the resulting table is organized as follows:


EXT	AREA	NUMBER
311	215	2155554242
358	303	5553869
320	214	2145559893
289	303	5555836
248	617	6175551999

TIP: 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')

The following is an excerpt from the resulting table:


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 containing the columns EXT, SUM_COST, and SUM_DUR:


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

TIP: 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
** Structure TABLE_GB_2, 3 tags, 12 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.

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')


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.


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')

Here is a portion of the resulting table.


DATE	TIME	DUR	INIT	EXT	COST	AREA	NUMBER
901004	95300	1.360	JAT	0	0.00	303	5553200
901004	94700	0.800	JAT	0	0.00	303	5553200
901003	91600	2.330	JAT	0	0.00	303	5553440
901002	94800	1.310	RLD	248	0.35	617	6175551999
901002	94800	16.23	TDW	289	0.00	303	5555836
901002	93200	21.40	TAC	311	5.78	215	2155554242
901002	94700	17.44	EBH	320	4.71	214	2145559893

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 command produces a table organized like the previous table, except the COST column is now sorted in descending order within each group of extensions. The following illustrates the new table organization:


DATE	TIME	DUR	INIT	EXT	COST	AREA	NUMBER
901003	91600	0.450	MLK	370	0.12	212	2125557956
901004	95100	0.160	MLK	370	0.00	303	5551245
901004	94900	1.930	SRB	379	0.52	818	8185552880
901003	91600	1.530	SRB	379	0.41	212	2125556618

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', 'KAR', 'OLL', 'ERD']

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')

QUERY_TABLE Function

Usage

Input Parameters

Returned Value

Keywords

Discussion

Boolean and Relational Operators Used in Queries

Calculation Functions Used with GROUP BY

Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

See Also