This clause, determines which columns to include.
Options
*
Specifies all rows.
AVG
Computes the numeric average of CURRENCY, DOUBLE, NUMERIC, REAL, INTEGER, DATE, or TIME data types. R:BASE rounds averages of integer values to the nearest integer value and currency values to their nearest unit.
COUNT
Determines how many non-null entries there are for a particular column item.
DISTINCT
Eliminates duplicate rows from the calculation.
LISTOF
Creates a text string of the values separated by the current comma delimiter character.
The LISTOF function can be used with the "SELECT ... INTO ..." to populate a variable with a list of values which can then be used in a CHOOSE command with the #LIST option. It can also be used in Forms, Reports or Labels to look up values from multiple rows.
MAX
Selects the maximum value in a column. This option accepts columns with any data type except BIT, VARBIT, VARCHAR, and BITNOTE.
MIN
Selects the minimum value in a column. This option accepts columns with any data type except BIT, VARBIT, VARCHAR, and BITNOTE.
SUM
Computes the numeric sum of CURRENCY, DOUBLE, REAL, NUMERIC, or INTEGER data types.
About SELECT Functions
A SELECT function can be used to provide summary data about a group of rows in a table or for all rows in a table. These functions may only be used with the GROUP BY clause or when only SELECT functions are specified.
Note:
Using MIN and MAX functions for NOTE data type. Select aggregate functions, such as MIN and MAX, require that we keep an accumulator and choose to only use the first 80 characters for NOTE values. This matches the fact that if you sort on NOTE fields, the sort will be based on the first 80 characters only.
Examples
The COUNT function works in two different ways, depending on its argument. COUNT(*) counts all rows in a table, but COUNT(colname) counts only rows in which the value in the specified column is not null. For example:
SELECT emptitle, COUNT(*), COUNT(emptitle) +
FROM employee GROUP BY emptitle
The final result contains both forms of the COUNT function.
emptitle COUNT(*) COUNT(emptitle)
Manager 2 2
Representative 3 3
Sales Clerk 1 1
-0- 2 0
If you wanted to compute the difference between each employee's average sales and the average sales for all employees, you would first use a SELECT function to calculate the average for all employees and store the result in a variable. For more information, see "INTO Clause" later in this entry.
The following command assigns the value $71,571.88 to the vaverage variable.
SELECT AVG(netamount) INTO vaverage FROM transmaster
Next, you can use the variable and another SELECT function in an expression to calculate the difference for each employee, and display the average net amount for each employee.
SELECT empid, AVG(netamount), +
(.vaverage - (AVG(netamount)))=30 +
FROM transmaster GROUP BY empid
empid AVG (netamount) (.vaverage - AVG(netamount))
102 $64,510.00 $7,061.88
129 $69,555.00 $2,016.88
131 $118,000.00 -$46,428.12
133 $44,380.00 $27,191.88
160 $114,850.00 -$43,278.12
165 $14,685.00 $56,886.88
167 $3,830.00 $67,741.88
Examples Using the LISTOF Function
SELECT (LISTOF(ColumnName)) INTO VariableName INDICATOR IndVar +
FROM TableView WHERE ...
In a Form, Report or Label Expression:
VariableName = (LISTOF(ColumnName)) IN LookUpTableView WHERE +
KeyColumn = KeyColumn
Example 01:
SET VAR vValueList TEXT = NULL
SELECT (LISTOF(ColumnName)) INTO vValueList INDIC IvValueList +
FROM TableName WHERE ...
The variable vValueList will be a text string of the values separated by the current comma delimiter character.
If you would like to add a single space after each value, then:
SET VAR vValueList TEXT = NULL
SELECT (SRPL(LISTOF(ColumnName),',',', ',0)) INTO +
vValueList INDIC IvValueList FROM TableName WHERE ...
Notice the additional space after comma in ReplaceString.
If you would like to use a carriage return after each value, then:
SET VAR vValueList TEXT = NULL
SELECT (SRPL(LISTOF(ColumnName),',',(CHAR(10)),0)) INTO +
vValueList INDIC IvValueList FROM TableName WHERE ...
Example 02:
CONNECT Concomp IDENTIFIED BY NONE
SET CAPTION ' '
SET AUTODROP OFF
SET RBGSIZE CENTER CENTER 800 600
SET VAR vLines INTEGER = 0
SET VAR vValueList TEXT = NULL
SET VAR vLastName TEXT = NULL
SET VAR vTitle TEXT = 'List Created Using LISTOF Function'
SET VAR vCaption TEXT = 'Using #LIST Options in CHOOSE Command!'
CLS
PAUSE 3 USING 'Collecting Values ...' CAPTION .vCaption AT 16 30
SELECT (COUNT(*)), (LISTOF(EmpLName)) INTO +
vLines INDIC IvLines, vValueList INDIC IvValueList FROM Employee
IF vLines > 18 THEN
SET VAR vLines = 18
ENDIF
CLS
CHOOSE vLastname FROM #LIST .vValueList AT 6 30 +
TITLE .vTitle CAPTION .vCaption LINES .vLines FORMATTED
IF vLastName IS NULL OR vLastName = '[Esc]' THEN
GOTO Done
ELSE
CLEAR ALL VAR EXCEPT vLastName
ENDIF
-- Do what you have to do here ...
LABEL Done
CLS
CLEAR ALL VAR
QUIT TO MainMenu.RMD
RETURN
Example 03:
If you would like to retrieve the list of DISTINCT values, then:
SET VAR vValueList TEXT = NULL
SELECT (LISTOF(DISTINCT ColumnName)) INTO +
vValueList INDIC IvValueList FROM TableName WHERE ...
