Official Content

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

Syntax

unique <Att1,...,AttN>

Where:

Att1,...,AttN
          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 that 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 Att1,...,AttN. 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, you need to list all the dates for which you 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 you need to list for each invoice date, the total amount for all the invoices for 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

See Also

Unique clause in For Each command for more examples
Unique clause in Data Providers
Option Distinct clause (replaced by Unique clause, maintained for compatibility reasons)



Last update: February 2024 | © GeneXus. All rights reserved. GeneXus Powered by Globant