"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 =约翰”,所以你有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.