QUERY_TABLE Function

PV-WAVE Foundation > Reference Guide > Procedure and Function Reference: Q > 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 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')