Select the maximum variation excluding deleted and archived records

By default, the maximum variation of each record, excluding those marked as deleted and archived, is returned in the result set.

This logic matches most transaction systems. If a record is deleted, then the references to that record are physically or logically deleted. The purpose of this logic is to make the Data Lake appear as a regular transaction database, where records that are physically or logically deleted do not appear in query results.

Since this is the default behavior for all queries, no query hint is required.

Query

select * from Products

This table shows the product records that are stored in Data Lake:

Company IdentifierPath ProductID IdentifierPath Description Price Variation VariationPath DeletedFlag DeleteIndicator ArchivedFlag ArchiveIndicator
003 12345 Skateboard 205 3 false false

The results include highest variation, variation 3, of the record for company 003, productId 12345. The record for company 001, product 991041 is not in the results because the maximum variation, variation 2, has a deleted status. The record for company 002, productId 222333 is not in the result set because the maximum variation, variation 2 has an archived status

There are two methods to select record variations. You can select records by using Infor table variation functions or variation query hints. You cannot use both in a query. Table functions are recommended if your query application does not support SQL hints