How to get the row number in a domain using a Virtual Data Source

Issue:

The domain is created using a virtual data source with MySQL as the database.

Is it possible to get the row numbers in the domain designer using derived tables and/or calculated fields ?

Solution:

Virtual Data Source is based on the TEIID engine. You can explore the various TEIID functions that can be used in SQL from this page :

https://docs.jboss.org/teiid/7.6.0.Final/reference/en-US/html_single/#d0e4503

There is the ROW_NUMBER() function which assigns a number to each row in a partition starting at 1.

This function can be used when creating a derived table.