Dataset node is used to retrieve data from Datasets using a query. The query editor supports ANSI SQL standard queries.

For Jiffy table and Document table, it is recommended to use Jiffy Select and Jiffy Insert/Update nodes.

Query Tab of Dataset Node

Double click the Dataset node and click the Query tab.

Query to be executed is specified in the Query field.

Parameters in the query can be passed in the following format:

1. String values: ‘$[String]’
2. Integer values: $[Integer]

Select * from `DatasetTest.csv`where Value = '$[param]'

Specify query to be executed in the Query field. Query must be in the format:

• If using Jiffy table: select * from `JiffyTableName`
• If using Dataset: select * from `CsvDatasetName.csv`
  

• The symbol used is backquote` and not single quote'
• No semicolon is required at the end of the query.

To select the columns from Customers dataset where the value in the column LastName is Smith, the query is:
Select * from `/Human Resource/Administration/EmployeeDetails.csv` where LastName ='Smith';

Processing Type

The query can be processed in two ways:

1. Row Wise Processing: Records in the dataset are processed row by row.
   Specify the following details:
   • Row Limit: Output row limit. By default, it is set to 500.
   • Columns in result set (space separated): Column names to display in the result set and must be space-separated.

     This is mandatory and the specified column names should be the same as the name of the columns in the dataset.

   • Iterate on this node’s output?: Toggle to ON, if you want to iterate on the output records. 

     Use Row Wise Processing when:

     • You need each row in the dataset to be fetched as an iteration for further automation.
     • You need to process less than 1000 records.

     
2. Bulk Processing: Records in the dataset are processed in bulk.
   Specify the following details:
   • Row Limit: Output row limit. By default, it is set to 500.
   • Table Name: Select the DataTable to which you want the records to be written. Default DataTables and Custom DataTables created in the task will be listed in the dropdown.
     To know more about DataTable, click here.

     Use Bulk Processing when:

     • All the records in the dataset must be used for further automation as a Datatable.
     • You need to process more than 1000 records.

     Row limit for Bulk Processing is 5000.

Properties of the Dataset Node

1. Navigate to Properties and enter Name and Description.
2. Enable Mark run Failure on Node Fail, Continue on no result? options as required.

Result of Execution of Dataset Node

After executing the task, the Result of Execution window displays the following tabs:

• Input: The query executed is displayed.
• Output: If Row Wise Processing operation was performed in the node, output tab displays the result of the executed query consisting of columns selected in Columns in result set (space separated) field.
  Click the Iteration # to see the details of each row.
  
• Run Info: The Run Details and Configuration Details are displayed.
  • Run Details include Run ID, Sequence Number, Iteration ID, and Total Node Execution Time(in seconds).
  • Configuration Details include Configuration Name, JDBC Driver Class, Username, and Config Level.
• Formatted Output: If Bulk Processing operation was performed in the node, Formatted Output tab displays the result set as a table.