4D Doc Center : SELECT

Home

4D v14

SELECT



	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] [LIMIT {4d_language_reference \|int_number \| ALL}] [OFFSET 4d_language_reference \|int_number] [INTO {4d_language_reference, ..., 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 LIMIT clause can be used to restrict the number of data returned by passing a 4d_language_reference variable or int_number.

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 INTO clause can be used to indicate 4d_language_reference variables to which the data will be assigned.

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.

Example 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;$StartYear;$EndYear)

 ARRAY INTEGER(aMovieYear;0)

 ARRAY LONGINT(aTicketsSold;0)

 $MovieYear:=1979

 $MinTicketsSold:=10000000

 $StartYear:=5

 $EndYear:=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

    ORDER BY 1

    LIMIT :$EndYear

    OFFSET :$StartYear

    INTO :aMovieYear, :aTicketsSold;

 End SQL

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

Example 3

Given a SALESREPS table where QUOTA is the expected sales amount for a sales representative and SALES is the actual amount of sales made.

 ARRAY REAL(arrMin_Values;0)

 ARRAY REAL(arrMax_Values;0)

 ARRAY REAL(arrTotal_Values;0)

 Begin SQL

    SELECT MIN ( ( SALES * 100 ) / QUOTA ),

    MAX( (SALES * 100 ) / QUOTA ),

    SUM( QUOTA ) - SUM ( SALES )

    FROM SALESREPS

    INTO :arrMin_Values, :arrMax_Values, :arrTotal_Values;

 End SQL

Example 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