ETL Validator uses inline views in a couple of places to retrieve the count of rows or to limit the data shown in the screen. However, some of the data sources do not like inline views as much. Starting from ETL Validator v3.4.6, the use of inline views can be avoided by following these two steps: 


Avoiding Count Queries

Count queries is issued by ETL Validator when executing a test case to show the progress of the data load. To retrieve the count from a DB Component while performing the data load, ETL Validator uses an inline view as shown below: 


select count(*) from (<Component SQL Query>);


Some of the data sources do not like inline views. This query can be avoided by following the below steps: 

  • Open ETL Validator Reporting by clicking the reports icon from the ETL Validator Client as shown below:


  • Login to Reports and navigate to  'Manage Source' page.


  • Select the data source you want to disable the count query for (eg. SQLServer) and disable the Count Query as shown below



  • Save the changes and restart ETL Validator Server and Client




Avoiding Inline Views while limiting rows

ETL Validator uses inline views to limit the number of rows displayed in the UI when an user tries to execute the SQL Query. This is required for some of the data sources because the corresponding JDBC driver returns all the rows from the SQL Query resultset thus causing 'out-of-memory' exceptions. For example, ETL Validator modifies the user entered query as shown below to restrict the number of rows displayed to the user: 


select * from (<Component DB Query>) limit 100;


ETL Validator v3.4.6 now supports a special parameter called 'p_limit' that can be embedded into the component DB Queries so that ETL Validator does not need to use inline views. Here are the steps for using the special parameter : 

  • Navigate to the parameters screen and create a new parameter called 'p_limit' if it does not exist already. The folder location does not matter. The value of the parameter can be something line ' limit 100'

  • Use the 'p_limit' parameter in your query where appropriate. ETL Validator will replace the 'p_limit' parameter with the corresponding value when executing the query to display rows in the UI. Since this is a special parameter, ETL Validator automatically ignore this parameter when executing the  query as part of a test case or a count query.