Command SELECT

SELECT

version 11.3 (Modified)

SELECT [ALL | DISTINCT]

{* | select_item, ..., select_item}

FROM table_reference, ..., table_reference

[WHERE search_condition]

[ORDER BY sort_list]

[GROUP BY sort_list]

[HAVING search_condition]

[OFFSET 4d_language_reference |int_number]

[LIMIT {4d_language_reference |int_number | ALL}]

[INTO {4d_language_reference, ..., 4d_language_reference | LISTBOX 4d_language_reference}]

[FOR UPDATE]

Description

The SELECT command is used to retrieve data from one or more tables.

If you pass *, all the columns will be retrieved; otherwise you can pass one or more select_item type arguments to specify each column to be retrieved individually (separated by commas). If you add the optional keyword DISTINCT to the SELECT statement, no duplicate data will be returned.

Queries with mixed "*" and explicit fields are not allowed. For example, the following statement:

SELECT *, SALES, TARGET FROM OFFICES

... is not allowed whereas:

SELECT * FROM OFFICES

...is allowed.

The FROM clause is used to specify one or more table_reference type arguments for the table(s) from which the data is to be retrieved. You can either pass a standard SQL name or a string. It is not possible to pass a query expression in the place of a table name. You may also pass the optional keyword AS to assign an alias to the column. If this keyword is passed, it must be followed by the alias name which can also be either an SQL name or string.

The optional WHERE clause sets conditions that the data must satisfy in order to be selected. This is done by passing a search_condition which is applied to the data retrieved by the FROM clause. The search_condition always returns a Boolean type value.

The optional ORDER BY clause can be used to apply a sort_list criteria to the data selected. You can also add the ASC or DESC keyword to specify whether to sort in ascending or descending order. By default, ascending order is applied.

The optional GROUP BY clause can be used to group identical data according to the sort_list criteria passed. Multiple group columns may be passed. This clause can be used to avoid redundancy or to compute an aggregate function (SUM, COUNT, MIN or MAX) that will be applied to these groups. You can also add the ASC or DESC keyword as with the ORDER BY clause.

The optional HAVING clause can then be used to apply a search_condition to one of these groups. The HAVING clause may be passed without a GROUP BY clause.

The optional OFFSET clause can be used to set a number (4d_language_reference variable or int_number) of data to be skipped before beginning to count for the LIMIT clause.

The optional LIMIT clause can be used to restrict the number of data returned by passing a 4d_language_reference variable or int_number.

The optional INTO clause can be used to indicate 4d_language_reference variables to which the data will be assigned. You may also pass the LISTBOX keyword to place the data into a 4d_language_reference listbox.

A SELECT command that specifies a FOR UPDATE clause attempts to obtain exclusive writing locks on all the selected records. If at least one record cannot be locked, then the whole command fails and an error is returned. If, however, all the selected records were locked, then they will remain locked until the current transaction is committed or rolled back.

Examples

1. Suppose that you have a movie database with one table containing the movie titles, the year it was released and the tickets sold for that movie.

We would like to get the years starting with 1979 and the amount of tickets sold where the total sold was less than 10 million. We want to skip the first 5 years and to display only 10 years, ordered by the year.


   C_LONGINT($MovieYear;$MinTicketsSold;$Offset;$Limit)
   ARRAY INTEGER(aMovieYear;0)
   ARRAY LONGINT(aTicketsSold;0)
   $MovieYear:=1979
   $MinTicketsSold:=10000000
   $Offset:=5
   $Limit:=10

   Begin SQL
      SELECT Year_of_Movie, SUM(Tickets_Sold)
      FROM MOVIES
      WHERE Year_of_Movie >= :$MovieYear
      GROUP BY Year_of_Movie
      HAVING SUM(Tickets_Sold) < :$MinTicketsSold
      OFFSET :$Offset
      LIMIT :$Limit
      ORDER BY 1
      INTO :aMovieYear, :aTicketsSold;
   End SQL

2. Here is an example where a combination of search conditions are used:

   SELECT supplier_id
   FROM suppliers
   WHERE (name = 'CANON')
   OR (name = 'Hewlett Packard' AND city = 'New York')
   OR (name = 'Firewall' AND status = 'Closed' AND city = 'Chicago');

3. Given the table SALES_PERSONS where QUOTA is the amount of sales expected to be earned by a given salesperson and SALES is the actual amount of sales made by a given salesperson.



   ARRAY REAL(Min_Values;0)
   ARRAY REAL(Max_Values;0)
   ARRAY REAL(Sum_Values;0)
   Begin SQL
      SELECT MIN ( ( SALES * 100 ) / QUOTA ), 
      MAX ( ( SALES * 100 ) / QUOTA ), 
      SUM ( QUOTA ) - SUM ( SALES )
      FROM SALES_PERSONS
      INTO :Min_Values, :Max_Values, :Sum_Values;
   End SQL

4. Here is an example which finds all the actors born in a certain city:

   ARRAY TEXT(aActorName;0)
   ARRAY TEXT(aCityName;0)
   Begin SQL  
      SELECT ACTORS.FirstName, CITIES.City_Name
      FROM ACTORS AS 'Act', CITIES AS 'Cit'
      WHERE Act.Birth_City_ID=Cit.City_ID
      ORDER BY 2 ASC
      INTO : aActorName, : aCityName;
   End SQL