Overview of the Select Statement
To read data from database tables into forms, fields, and variables, use the select statement. The select statement takes values from the columns in the specified database table for all rows that satisfy the condition specified in an optional where clause.
Any field can be the target of a select query. 4GL allows you to select into a simple field, a table field, a table field row, a table field cell, record, array, an attribute of an array, or an attribute of a record.
The select statement can contain several clauses: the select clause, the from clause, and the optional where, order by and union clauses. The where clause can contain a further optional qualification clause. The following shows a simplified syntax for select:
objectname = select
[fieldname = ] [tablename.]columnname
{, [fieldname = ] [tablename.]columnname ]
from tablename
[where qual ]
[order by orderlist];
Select syntax including the union clause is shown in The Union Clause. When field names in different tables are the same, use tablename.columnname format to identify the source tables.
This example of a select statement contains a from and a where clause:
deptform := select lname, fname
from personnel
where empnum = 7;
In this example, Lname and Fname are columns in the Personnel table. The form Deptform has similarly-named simple fields. Values returned for Lname and Fname are displayed in the corresponding simple fields. The query retrieves the first and last name of employee number 7.
The select statement can contain the following clauses:
• The select clause names the database columns from which you want to retrieve data. In the example, Lname and Fname are the names of the columns.
• The from clause specifies the database tables to which the columns belong. In the example, this is the Personnel table.
• The where clause allows you to fine-tune your query by specifying conditions you want the selected columns to meet. This is useful for a simple-field assignment. In the example, the where clause limits the query results to the name of employee number 7. For more details, see The Where Clause.
To retrieve many rows (as in a table-field or array assignment), use a where clause that specifies several rows. This example retrieves employees who make more than $20,000:
perstable := select lname, fname, salary
from personnel
where salary >= 20000;
The where clause is optional, except in the detail portion of Master/Detail query.
You can use the optional qualification function in a where clause. See The Qualification Function.
• The optional order by clause allows you to specify sorting criteria when a query returns more than one value. The following example retrieves more than one row; the statement orders them by the named column, Lname.
depttable := select lname, fname
from personnel
where emptitle = manager
order by lname;
When you sort, you must specify field names from the complex object into which you are selecting. These are the same as column names in the database table.
• The optional union clause combines the results of a number of select statements. For more information, see The Union Clause.
From Clause
The from clause specifies the source tables from which data is to be read. The specified tables must exist at the time the query is issued. The from_source parameter can be:
• One or more tables, specified using the following syntax:
[owner.]table [corr_name]
where table is the name of a table, owner is the name of the user that owns the table, and corr_name is a correlation name for the table. A correlation name is an alternate name for the table, often used to abbreviate long table names. If you assign a correlation name to a table, you must refer to the table using the correlation name. The table name can be a 4GL name; however, if a 4GL variable is used, an owner cannot be specified, unless the owner name is included as part of the value. In other words, the variable can contain the value owner.tablename.
• A join between two or more tables, specified using the following syntax:
source join_type join source on search_condition
where:
– The source parameter is the table or outer join where the data for the left or right side of the join originates.
– The join_type parameter specifies inner, left, right, or full outer join. The default join type is inner.
– The search_condition is a valid restriction, subject to the rules for the where clause. The search condition must not include aggregate functions or subselects.
For example:
select e.ename, d.dname from
(employees e left join departments d
on e.edept = d.ddept);
You can specify a maximum of 126 tables in a query, including the tables in the from list and tables in subqueries.
See the SQL Reference Guide for more information about outer joins.
Where Clause
The optional where clause qualifies a database retrieval according to the specified logical (Boolean) expressions. The where clause is simply a Boolean combination of conditions. A condition can be an SQL predicate (for example, [tablename.]columnname = expression) or it can be an invocation of the qualification function.
You can use a simple logical expression, which compares a column in a database table to an expression, such as a constant value or a field in the current form. For example:
empform := select name, jobtitle
from employee
where salary >= :sallimit;
This statement selects into Empform the names and job titles of all employees whose salaries are equal to or greater than the value entered in the Sallimit field.
In SQL, you can use the underscore (_) and the percent sign (%) as wildcard characters. The underscore (_) represents a single character, and the percent sign (%) represents any number of characters. The available wildcard characters depend on the installation parameters of the II_PATTERN_MATCH logical/environment variable. For a complete list of comparison operators, see Using Logical Expressions.
The II_PATTERN_MATCH logical/environment variable sets pattern matching to SQL or QUEL. Unless II_PATTERN_MATCH is set, you must use the wildcard characters for the query language of the application. By default, II_PATTERN_MATCH specifies QUEL pattern-matching characters, which are different from those in SQL. For more information, see
Notes for Users of QUEL.
You can use a 4GL string variable to specify the qualification for the where clause. For example, you can declare a variable "whclause" and assign as its qualification the following value:
whclause = 'emp.salary > 40000';
You then can use this variable as the qualification for the where clause in your select statement, as follows:
select * from emp
where :whclause;
Queries in which the where clause is specified in a variable are not suitable for use as repeated queries. The keyword repeated is ignored if you specify it for such queries.
When using variables or constants in select statements in ABF, any variable or constant on the left side of a comparison operator is treated as a column name. Any variable or constant on the right side is treated as a value.
For example, in the following query:
select * from :a where :b = :c
:a is treated as a table name, :b is treated as a column, and :c is treated as a value (and therefore may have quotes surrounding it depending on the data type).
The entire where clause of a query can be put into a single variable (for example, where :a).
Note: The phrase ":a between x and y" is treated as a column name, as is ":a = (select ....)". In those cases, if you wish to use :a as a value, either the query must be rewritten (for example, mycolumn >= :a and mycolumn <= :a) or an override can be used to force all parameters in the where or on clauses to be used as values (and thus have quotes where required). To set this override, issue the command ingsetenv II_ABF_ASSUME_VALUE Y or set II_ABF_ASSUME_VALUE to Y in the environment where the ABF application is compiled.
Qualification Function
The qualification function, when included in a where clause, allows you to set up the exact search criteria at run time in the specified simple fields or table field columns of the current form. The qualification function interprets the values in these fields as expressions, and builds the where clause on these. However, the qualification function cannot be used when the where clause is specified in a variable, as described above.
Placing the optional qualification function in a where clause allows the end user to include comparison operators (such as != or <) and wildcard characters at run time in the search condition. The end user can type values, comparison operators, or wildcard characters into the visible simple fields or table field columns in the current form specified by the qualification function.
The requirements for using the qualification function are:
• The current form must be in Query mode.
• In a qualification function, only simple fields or table field columns on a form (not local variables) are allowed.
• Any table field on which you allow qualifications must be in query mode.
When the end user enters query qualifications in a table field, the where clause is generated as follows:
• Qualifications in table field columns of a single row are joined by AND
• Qualifications in multiple table field rows are joined by OR
The example below shows a query operation that utilizes the qualification function on simple fields of a form:
empform := select idnum = empnum, jobtitle,
salary = empsal
from employee
where qualification (jobtitle = jobtitle,
empsal = salary);
By entering the values "%Manager%" and "<30000" in the jobtitle and salary fields, respectively, you can retrieve information on all employees having a job title containing the word "Manager," and whose salaries are under $30,000.
Without the qualification function in the original example, the where clause searches for a literal match to everything you enter in the field, including the operators > and <. If you enter no values, no restrictions are placed on the retrieval. In this case, a validation error results.
In the select example below, the order number and customer name correspond respectively to the orderno and custname fields on the addorders form:
addorders := select orders.orderno,
customer.custname
from orders, customer
where qualification
(orders.orderno = orderno,
customer.custname = custname) and
orders.custno = customer.custno;
If you enter ">1000" in the orderno field and "A%" in the custname field, the query retrieves all customers whose names start with "A" and whose order number is greater than "1,000." If you leave blank a field named in the qualification function, no qualification is generated for that field.
The following example shows a query operation that utilizes the qualification function on columns of a table field:
emptf := select empnum, jobtitle, empsal
from employee
where qualification
(jobtitle = emptf.jobtitle,
empsal = emptf.empsal):
Queries in which the qualification function is used are not suitable for use as repeated queries. The keyword repeated is ignored if you use it with such queries.
Order By Clause
If more than one orderfieldname is specified in the order by clause, rows are ordered on the basis of the first orderfieldname, second orderfieldname, and so on, and within values of that field, on the basis of the specified sortorder. The syntax for the order by clause is as follows:
order by orderfieldname [sortorder]
{, orderfieldname [sortorder]}
Specify a sortorder of asc for ascending (the default), or desc for descending.
Note that you must specify the result names in the order by clause in any select statement. Because 4GL gives the field name as the result name for columns and target expressions, you must sort by the field names in 4GL, as in the following example:
empform := select ename, dept
from employee
order by ename;
When you are selecting to a different name, order by the result name:
result = columnname
or
columnname as result
In this example, the query sorts by the result (field) name, person, and not by the column name Ename.
newform := select person=ename, dept
from employee
order by person;
Union Clause
Use the union clause to combine the results of a number of select statements. The syntax for this clause was not shown in the main syntax for the select statement to save space. The general syntax is:
objectname :=subselect
union [all] subselect {union [all] subselect}
Here subselect is a select statement without the repeated keyword or the order by clause. The subselect can optionally be enclosed in parentheses. Any number of subselects can be joined together with the union clause.
The names in the column list for the first subselect build the correspondence to the form fields or variables. A name is also a literal. All result rows of any select statement in a union must have the same data type. However, the column names do not have to be the same. The following select statement selects the names and numbers of people from different organizations:
empform := select ename, enumber
from employee
union
select dname, dnumber
from directors
where dnumber <= 100;
A subselect refers to an expression or a form field instead of a column in the database table. In this case, you must use the following syntax for the name in the column list:
fieldname = expression | form_field
For example:
empform := select ename, compens = esal + ebens
from employee
union
select dname, compens = dsal + dbens
from directors;
or
empform := select ename, compens = esal + ebens
from employee
union
select dname, compens = :total_benefits
from directors;