"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

uday

Dear Marius,

Thanks for your quick reply.

I would like to know in detail about the two approaches you have mentioned in the reply.

This information provided by you is useful, but it would be great if you can guide us by providing some more concrete

examples especially in the second approach.

Thanks in Advance

waiting for your reply.