User Guide > Creating and Querying Tables > Table Function Overview
  

Table Function Overview
This quick overview is intended to give you a feel for the capabilities of the table functions. Greater detail on all aspects of these functions is provided throughout the rest of this chapter.
Assume that a company-wide telephone system automatically collects data on various aspects of a company’s telephone calls. The system collects the date and time of each call, the caller’s initials, caller’s extension number, area code of call, phone number of call, call duration, and cost. This information is collected and stored in a data file.
After you read this data into PV‑WAVE, you can use the BUILD_TABLE function to create a table. Once the table is created, you can use QUERY_TABLE to subset the data in various ways.
Here are some typical table queries using the QUERY_TABLE function. Assume that the name of the table (which is specified when the table is created) is phone_data. The names of the table’s columns are just as they appear below. Don’t worry now about the details of how the functions work, similar queries are explained in detail later in this chapter.
 
DATE
TIME
DUR
INIT
EXT
COST
AREA
NUMBER
901002
093200
21.40
TAC
311
5.78
215
2155554242
901002
094700
1.05
BWD
358
0.0
303
5553869
901002
094700
17.44
EBH
320
4.71
214
2145559893
901002
094800
16.23
TDW
289
0.0
303
5555836
901002
094800
1.31
RLD
248
.35
617
6175551999
901003
091500
2.53
DLH
332
.68
614
6145555553
901003
091600
2.33
JAT
000
0.0
303
555344
901003
091600
.35
CCW
418
.27
303
5555190
901003
091600
1.53
SRB
379
.41
212
2125556618
901004
094700
.80
JAT
000
0.0
303
555320
901004
094900
1.93
SRB
379
.52
818
8185552880
901004
095000
3.77
DJC
331
1.02
512
5125551228
Create a subset of the table that only shows the date, duration, and extension of calls made.
tbl = QUERY_TABLE(phone_data, 'DATE, DUR, EXT')
Show all of the calls made on October 2, 1990.
tbl = QUERY_TABLE(phone_data, '* Where DATE = 901002')
Sort the table in descending order, by cost.
tbl = QUERY_TABLE(phone_data, '* Order By COST Desc')
Sort the table first in ascending order by date, then within each group of dates by cost in descending order.
tbl = QUERY_TABLE(phone_data, '* Order By DATE, COST Desc')
Show the total cost incurred from each telephone extension on October 3.
tbl = QUERY_TABLE(phone_data, $
'EXT, Sum(COST) Where DATE = 901003,' + 'Group By EXT')
 
note
The second parameter in a QUERY_TABLE call is one string. The plus sign (+) used above is the string concatenation operator. It is used because it is not legal otherwise to break a string onto multiple lines within a PV‑WAVE command.
For each extension, what was the average cost of out-of-state calls from October 3 to October 6?
tbl = QUERY_TABLE(phone_data, 'EXT, Avg(COST) ' + $
'Where (DATE >= 901003 AND DATE <= ' + $
'901006) AND (AREA <> 303), Group By ' + 'EXT')
Show the data on all of the calls that cost less than $5.00.
tbl = QUERY_TABLE(phone_data, '* Where COST < 5.0')
Show the calls made by the caller with initials TAC.
tbl = QUERY_TABLE(phone_data, '* Where INIT = "TAC"')
Show the extension, date, and total duration of all calls made from each extension on each date.
tbl = QUERY_TABLE(phone_data, $
'EXT, DATE, Sum(DUR) Group By EXT, DATE')

Version 2017.0
Copyright © 2017, Rogue Wave Software, Inc. All Rights Reserved.