4D v13.4
SELECT
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] |
||
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.
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 SQLHere 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'); 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 SQLHere 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
Product: 4D
Theme: SQL Commands
4d_language_reference
search_condition
select_item
sort_list
subquery
table_reference
