DynamicInput

​

Overview

Use the DynamicInput gem to run SQL queries or read data files, dynamically automating data retrieval. This page describes how to either modify SQL queries or dynamically read data from multiple files.

​

Prerequisites

• Run Prophecy version 4.1.3 or higher.

​

Read Option 1: Modify SQL Query

Run parameterized SQL queries that automatically update based on incoming data. Define a single SQL template with placeholders, and at runtime, DynamicInput replaces those placeholders with column values from your input dataset.

​

Input and Output

Configure the following input and output ports.

​

Parameters

Configure the DynamicInput gem using the following parameters.

​

Example

This example demonstrates how to filter rows from an Oracle table by replacing placeholder text with values from your dataset.

1. Under Select Read Options, select Modify SQL Query.
2. Under Table Connection Type, select Oracle.
3. Select an existing connection or create a new one.
4. In Table or Query, enter:
   SELECT *
   FROM SALES.ORDERS
   WHERE status <> 'replace'
   AND region <> 'AAA'
   
5. In the Replace a Specific String table, set:
   • Text to Replace: replace
   • Replacement Field: status_column

When you run the gem, it will take each row from your input dataset, replace the placeholder text (replace) in the SQL template with the corresponding value from status_column, and execute that query. Prophecy will then union all of the query results into a single output dataset, so you can work with them as one combined table.

​

Read Option 2: Dynamically read data from multiple files

Combine data from listed XLSX files, or extract only the sheet names from listed files and append them as values in a new column.

​

Input and Output

Configure the following input and output ports.

​

Parameters

Configure the DynamicInput gem using the following parameters. The following additional parameters are applicable to the Union sheet data by column name option only.