Select the maximum variation excluding deleted 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 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
002	222333	Scooter	132	2	false
003	12345	Skateboard	205	3	false

The results include highest variation, variation 2, of record the for company 002, productID 222333, and the 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.

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