Unique Clause

Official Content
This documentation is valid for:

Allows to specify attributes that should not have duplicated values in the output of the corresponding query.

Syntax

unique <attributeList>

where:

<attributeList>

list of attributes (including formula attributes) that are going to be processed together —only once in the output. Thus, if two or more records have the same values for that list of attributes, only one of them is taken into account. The attributes should belong to the extended table corresponding to the query and place restrictions on the other attributes than can be used at that level (eg the other for each command attributes): those other attributes also have to assume unique values based on the values of the <attributeList>. That is to say: they have to be unique, as well (will be explained below, by means of some examples). 

Description

There are two scenarios:

  • simple one: to avoid duplicates. For example, we need to list all the dates for which we have invoices, without repeating dates: 
for each Invoice
unique InvoiceDate
   print datePB //print block with InvoiceDate attribute
endfor
  • more complex: to show groups of data with aggregation. For example, if we need to list for each invoice date, the total amount for all the invoices of that date:
for each Invoice
unique InvoiceDate
   &total = Sum( InvoiceTotal)
   print totalbyDate //print block with: InvoiceDate, &total
endfor

Here the for each base table is the same as the Sum aggregate inline formula base table. Thus, the formula will add from the context an implicit condition for the evaluation: it will sum all the invoices for the given unique attribute, InvoiceDate, as the navigation report shows:

Unique with aggregation - navigation report

Restrictions

  1. Expressions are not allowed in the attributes list of the unique clause (eg "unique CustomerBirthDate.Year()").
  2. Only attributes that have a unique value from those of the unique clause can be included in the body of the For Each command or the Data provider group statement, outside aggregate formulas. By 'body' we mean the code related to each iteration, which leaves out the clauses order, using, where, etc. That is, the restriction applies once the records are filtered, ordered, etc. For the resulting set, is executed the body code.
  3. There can be no nesting levels. For now it can not be used to implement control breaks.

Restrictions examples

Consider the following example (where InvoiceId attribute is not unique for that records that have a given value for InvoiceDate):

for each Unique InvoiceDate
    print PB //print block with: InvoiceDate, InvoiceId
Endfor

This example will throw an error at specification time, because InvoiceId attribute has not a unique value for InvoiceDate.

The specifier always indicates whether the For Each is valid. Otherwise, it throws an error as the following:

error: spc0206: Cannot reference attribute(s) InvoiceId unless they are referenced in Unique clause in group starting at line 11.

In case of formulas, the rule is that:

  • Horizontal Formulas included in the body of the For Each: the attributes of the formula have to be reachable with a unique value from the attributes of the unique clause.
  • Aggregate Formulas: the attributes of the formula do not need to be reachable with a unique value from the attributes of the unique clause.

That is why the following example is valid. We want to print, for each customer with invoices from a certain date, his/her name and the total amount of those invoices:

for each Invoice
unique CustomerName
where InvoiceDate >= &fromDate
   &total = Sum( InvoiceTotal, InvoiceDate>=&fromDate)
   print customerInfo //printblock with: CustomerName, &total
endfor

Note InvoiceDate is not reachable with a unique value from CustomerName, but InvoiceDate is not in the body of the for each, outside an aggregate formula. It is only in the where clause, acting as a filter to obtain the records over with the body code itself will be executed. It is also in the Sum definition, indicating a filter over the records to be navigated in order to evaluate the formula.

Scope

Commands:  For Each command, Data provider Group statement

See also

 



Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.