Select the maximum variation excluding deleted and archived records

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 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