Given a database table how to use macros and loop to change the multiple "where" values of SELECT

Somnath_Das

Given a database table how do I use macros to change the multiple "where" values of a select query using rapidminer? I have created a sample process but there the WHERE clause is having a single condition.. I want to check for multiple conditions.

For example if I want to check like ACCOUNT_NO & CUSTOMER_ID how do I use macros and loops so a subprocess will run the following statement?

Select * from Table where account_no=suppliedValue_1 and customer_id=suppliedValue_2

<?xml version="1.0" encoding="UTF-8"?><process version="9.9.000">

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

  <operator activated="true" class="process" compatibility="9.4.000" expanded="true" name="Process" origin="GENERATED_TUTORIAL">

    <parameter key="logverbosity" value="init"/>

    <parameter key="random_seed" value="2001"/>

    <parameter key="send_mail" value="never"/>

    <parameter key="notification_email" value=""/>

    <parameter key="process_duration_for_mail" value="30"/>

    <parameter key="encoding" value="SYSTEM"/>

    <process expanded="true">

      <operator activated="true" class="retrieve" compatibility="9.9.000" expanded="true" height="68" name="Retrieve card_fraud_txns_customer" width="90" x="45" y="34">

        <parameter key="repository_entry" value="../data/card/card_fraud_txns_customer"/>

      </operator>

      <operator activated="true" class="concurrency:loop_values" compatibility="9.9.000" expanded="true" height="82" name="Loop Values" width="90" x="179" y="34">

        <parameter key="attribute" value="CUST_CARD_ID"/>

        <parameter key="iteration_macro" value="loop_value"/>

        <parameter key="reuse_results" value="false"/>

        <parameter key="enable_parallel_execution" value="true"/>

        <process expanded="true">

          <operator activated="true" class="jdbc_connectors:read_database" compatibility="9.9.000" expanded="true" height="82" name="Read Database" width="90" x="179" y="34">

            <parameter key="define_connection" value="repository"/>

            <parameter key="connection_entry" value="/Connections/ML_UAT_DB"/>

            <parameter key="database_system" value="MySQL"/>

            <parameter key="define_query" value="query"/>

            <parameter key="query" value="select * from card_fraud_all where CUST_CARD_ID = ?"/>

            <parameter key="use_default_schema" value="true"/>

            <parameter key="prepare_statement" value="true"/>

            <enumeration key="parameters">

              <parameter key="parameter" value="VARCHAR.%{loop_value}"/>

            </enumeration>

            <parameter key="datamanagement" value="double_array"/>

            <parameter key="data_management" value="auto"/>

          </operator>

          <connect from_op="Read Database" from_port="output" to_port="output 1"/>

          <portSpacing port="source_input 1" spacing="0"/>

          <portSpacing port="source_input 2" spacing="0"/>

          <portSpacing port="sink_output 1" spacing="0"/>

          <portSpacing port="sink_output 2" spacing="0"/>

        </process>

      </operator>

      <operator activated="true" class="append" compatibility="9.9.000" expanded="true" height="82" name="Append (2)" width="90" x="313" y="34">

        <parameter key="datamanagement" value="double_array"/>

        <parameter key="data_management" value="auto"/>

        <parameter key="merge_type" value="all"/>

      </operator>

      <connect from_op="Retrieve card_fraud_txns_customer" from_port="output" to_op="Loop Values" to_port="input 1"/>

      <connect from_op="Loop Values" from_port="output 1" to_op="Append (2)" to_port="example set 1"/>

      <connect from_op="Append (2)" from_port="merged set" to_port="result 1"/>

      <portSpacing port="source_input 1" spacing="0"/>

      <portSpacing port="sink_result 1" spacing="0"/>

      <portSpacing port="sink_result 2" spacing="0"/>

      <background height="232" location="//Samples/Tutorials/Basics/01/tutorial1" width="1502" x="26" y="47"/>

    </process>

  </operator>

</process>

kayman

Same logic, but use 'generate macro' instead of set macro. Your logic then defines the value assigned to the macros. 

kayman

You can use the set macros operator. In this one you define the values like acountnumber = 1234.

In you database query you then use the macro syntax, like %{acountnumber}

That's actually it. You can create as many macros as you need, so your select statement would look like

Select * from Table where account_no=%{acountnumber} and customer_id=%{custid} 

Somnath_Das

I want to assign it through macros dynamically.

kayman

Same logic, but use 'generate macro' instead of set macro. Your logic then defines the value assigned to the macros. 

BalazsBarany

Hi!

It's a good practice to use prepared queries, enter the paramaters with their types (in your case, the %{macros} in this syntax) and refer to the parameters with ? (question marks) in the appropriate order.

This results in a better performance with many database systems, and avoids SQL injections.

Regards,
Balázs