"dynamic generation of sql query using macros"

udayuday Member Posts: 15 Contributor II
edited June 2019 in Help
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:


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

  Select * from EmployeeDetails where EmployeeName='Nancy' OR EmployeeName='Rene'OR

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?


  • Options
    MariusHelfMariusHelf RapidMiner Certified Expert, Member Posts: 1,869 Unicorn
    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,
  • Options
    udayuday Member Posts: 15 Contributor II
    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.
Sign In or Register to comment.