Column Reference

Column reference

Variants:
1. fdescriptor
2. MAX( [DISTINCT] fdescriptor )
3. MIN( [DISTINCT] fdescriptor )
4. AVG( [DISTINCT] fdescriptor )
5. SUM( [DISTINCT] fdescriptor )
6. COUNT( DISTINCT fdescriptor )
7. COUNT( * )

Effect

Used in the SELECT and ORDER BY clause of a SELECT or OPEN CURSOR statement to identify a column of the selection result by its field descriptor fdescriptor . You can use the field descriptor fdescriptor either on its own or as an argument in an aggregate expression. Aggregate expressions use an aggregate function (MAX, MIN, AVG, SUM or COUNT) to summarize data from one or all of the columns in a database table in the selection result.


Addition:
... AS alias

Effect

The column of the selection result named in the AS condition corresponds to a column of a Database table or view spcified in the FROM clause.

TABLES SCUSTOM. 
 
SELECT ID NAME 
	 FROM SCUSTOM 
	 INTO (SCUSTOM-ID, SCUSTOM-NAME). 
  WRITE: / SCUSTOM-ID, SCUSTOM-NAME. 
ENDSELECT.

Effect

For this column in the selection result, the alternative column name alias is used in the INTO- or ORDER-BY clause. You can use an alternative column name with INTO CORRESPONDING FIELDS OF ... in the INTO clause to assign a column of the selection result to a component of the target area with the name alias.

Example

Output a list of the customer numbers of all customers on all Lufthansa flights on 02.28.1995:

TABLES: SBOOK, SCUSTOM. 
 
SELECT DISTINCT CUSTOMID AS ID 
	 FROM SBOOK 
	 INTO CORRESPONDING FIELDS OF SCUSTOM 
	 WHERE 
		 CARRID   = 'LH '	AND 
		 FLDATE   = '19950228'. 
  WRITE: / SCUSTOM-ID. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the largest value for the selected lines in the column identified by the Field descriptor fdescriptor.The DISTINCT specification does not alter the result. NULL values are ignored in the calculation, except when all of the values in a column are NULL. In this case, the result is NULL.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the highest price paid, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 MAX( SBOOK~LOCCURAM ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-LOCCURAM) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-LOCCURAM. 
ENDSELECT.

Effect

As in variant 1. You can also use an alternative column name in variants 2 - 6 to sort the result by an aggregate expression. Unlike aggregate expressions, you can use alternative column names in the ORDER-BY clause.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the highest price paid, sorted by price and customer name:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 MAX( SBOOK~LOCCURAM ) AS MAX 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-LOCCURAM) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	ORDER BY MAX DESCENDING SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-LOCCURAM. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the smallest value within the selected lines in the column identified by Field descriptor fdescriptor. The DISTINCT specification does not affect the result. NULL values are ignored in the calculation, except when all of the values in the column are NULL. In this case, the selection result is NULL.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the lowest price, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 MIN( SBOOK~LOCCURAM ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-LOCCURAM) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-LOCCURAM. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the average value of the selected lines in the column identified by the Field descriptor fdescriptor . You can only use AVG for numeric fields. NULL values are ignored in the calculation, except when all values in the column are NULL . In this case, the result is NULL.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the average price, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 AVG( SBOOK~LOCCURAM ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-LOCCURAM) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-LOCCURAM. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the sum of all values in the selection range for the column identified by the Field descriptor fdescriptor . You can only use SUM for numeric fields. NULL values are ignored in the calculation, except when all values in the column are NULL . In this case, the result is NULL.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the sum of the prices, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 SUM( SBOOK~LOCCURAM ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 SBOOK-LOCCURAM) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
		 SBOOK-LOCCURAM. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the number of different values in the selection for the column identified by the Field descriptor fdescriptor . You must use the DISTINCT addition with this aggregate function. NULL values are ignored in the calculation, except when all of the values in the column are NULL. In this case, the function returns the value 0.

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the number of different prices paid, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
DATA:   COUNT TYPE I. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
		 COUNT( DISTINCT SBOOK~LOCCURAM ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 COUNT) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, COUNT. 
ENDSELECT.


Addition:
... AS alias

Effect

Returns the number of lines selected. If the SELECT command contains a GROUP-BY clause , the number of lines in each group is returned. You can write COUNT(*) as well as COUNT( * )

Example

Output a list of all customers on Lufthansa flight 0400 in 1995, along with the number of bookings they made, sorted by customer name:

TABLES: SCUSTOM, SBOOK. 
DATA:   COUNT TYPE I. 
 
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY COUNT( * ) 
	 INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, 
			 COUNT) 
	 FROM SCUSTOM INNER JOIN SBOOK 
		 ON SCUSTOM~ID = SBOOK~CUSTOMID 
	 WHERE SBOOK~FLDATE BETWEEN '19950101' AND '19951231' AND 
			 SBOOK~CARRID   = 'LH '						 AND 
			 SBOOK~CONNID   = '0400' 
	 GROUP BY SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
	 ORDER BY SCUSTOM~NAME. 
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY, COUNT. 
ENDSELECT.

Effect

As in variant 2.