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