Scope
Languages:
Microsoft .NET, C/SQL, Visual Basic, Visual FoxPro, Java, PocketPC
DBMSs: DB2 UDB for iSeries, DB2 Universal
Database, Informix, Oracle, SQL Server, PostgreSQL
Description
In some
cases, it is necessary to obtain different values from a set of attributes. This
can be worked out by using a control break, through the relevant attributes. In
order to solve this in a more efficient way, the option distinct clause has been
added to the For Each and XFor Each commands.
For example, suppose there is a table of Persons:
PerCod* Code
PerNam Name
PerEmail Email address
The way to obtain all the different e-mail addresses, without
duplicates, is the following:
For Each
Option
Distinct
&email =
PerEmail
Endfor
In order to determine the set of different values, the
attributes referenced in the For each are taken into account.
General considerations
-
The Option Distinct clause in For Each commands is supported by the
Client/Server generators and does not apply to local tables.
- For Each commands with option distinct clauses cannot update the
database. The same applies for internal For Each commands in control breaks. For
Each commands nested in other tables can be updated.
- If an order is not specified, Order None prevails.
- The Option Distinct clause is supported in the
"external" For Each commands with control breaks. The "internal" For Each
commands inherit the Distinct Clause.
- NULLs are
treated as any other value. This means that if a referenced attribute has a DBMS
NULL value in more than one record, the For Each command will only retrieve one
of them.