DEVTEST Read Rows from jdbc data source work with Test and Keep, but fails in ITR

Document ID : KB000122977
Last Modified Date : 13/12/2018
Show Technical Document Details
Issue:
 The Test step uses  a Read rows from JDBC data source . The sql Query in the test step has filters as shown below

 @fromDate = '{{=doDateDeltaFromCurrent("yyyy-MM-dd","{{=-LookBackDays}}D");/*2014-10-08*/}} 00:00:00.000', 
@toDate = '{{=doDateDeltaFromCurrent("yyyy-MM-dd","{{=-LookBackDays+9}}D");/*2014-10-08*/}} 23:59:59.999', 
@createDateFrom = '{{=doDateDeltaFromCurrent("yyyy-MM-dd","{{=-LookBackDays}}D");/*2014-10-08*/}} 00:00:00.000', 
@rowsCountToSelect = {{cntTrackno}} 


Running this via "Test and keep"  is successful, but running in ITR results in a java error: 

| Exception: 
============================================================================ 
| Message: Error doing random data sets 
---------------------------------------------------------------------------- 
| Trapped Exception: Error reading dataset definition Please make sure any dependent file(s) are available at the path specified in the test case on the host named US-ABCD. 
Environment:
All supported DEVTEST environments
Cause:
Filters in datasets are not recommended. The order of data sets execution is inconsistent as filters execute at the start and the end of each test step. 
Resolution:
We recommend not adding filters in data sets. The order of data sets execution is inconsistent while filters execute at the start and the end of each test step. 


https://docops.ca.com/display/DTS101/Data+Sets+and+Properties 

Workaround:
**************
Build the entire SQLStatement in  an output log step  or in "Execute script (JSR-223),  Then pass that SQL Query  as property in the JDBC step.