"dynamic generation of sql query using macros"

uday

I would like to construct the query dynamically for the readDatabase operator,
using macros i have achieved the dynamic construction partially.

In readdatabase operator query is like

Select * from EmployeeDetails where EmployeeName= %{empName}

where empName is macro defined in the process context.

Now i am looking to fetch the records of the table dynamically for
different attribute values.

The details of the table and the query is as follows:

Table:EmployeeDetails
---------------------

Sl No.  EmployeeName  age  sex  salary  1            Alex      34  M    25000 2            Nancy      25  F    20000 3            Zeena      42  F    78000 4            Thomas  38  M    60000 5            John      54  M    90000 6            Peter      39  M    70000 7            Martin    28  M    40000 8            Edgar      43  M    39000 9            Rene      22  F    32000 10          Johnson    56  M    88000

Query:
------
  Select * from EmployeeDetails where EmployeeName='Nancy' OR EmployeeName='Rene'OR
EmployeeName='John'

Is it possible make this query dynamically using macros?

Is it possible for a macro to take 'n'(where n is an integer greater than one) number of values?

MariusHelf

That is not possible out of the box. The macro would have to hold the complete expression "EmployeeName='Nancy' OR EmployeeName='Rene'OR
EmployeeName='John'", so you have to construct it yourself with a rather complicated Loop / Generate Macro construct.

To make your life easy, there are two possibilities:
- easy (and slow): use Loop Values over the input data, execute the query for the current employee and append the results
- faster (for many employees): Upload the table containing the employees to be queries into a temporary table in the database and perform a join inside the database

Best regards,
Marius