building up dynamic SQL queries

rschilling

I am not certain how to do this, although my research into macros seems to indicate they're involved somehow.

I have a database table (MySQL) with some meta-data in it.  Say, one column in the data is a table name:

select tbl_name from mymetadata;

That returns a list of table names that I want to feed into a SQL statement.  So, for each item from tbl_name, I want to run:

select count(*) from %{tbl_name}

How do I do this?

Thanks in advance..

Richard Schilling

TobiasMalbrecht

Hi Richard,

you should iterate over the examples, extract a macro for the table name and use the [tt]Read Database[/tt] operator in the loop to read the tables. It should work like this...

<process version="5.0">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" expanded="true" name="Process">
    <process expanded="true" height="145" width="279">
      <operator activated="true" class="read_database" expanded="true" height="60" name="Read Database" width="90" x="45" y="30"/>
      <operator activated="true" class="loop_examples" expanded="true" height="76" name="Loop Examples" width="90" x="179" y="30">
        <process expanded="true" height="419" width="492">
          <operator activated="true" class="extract_macro" expanded="true" height="60" name="Extract Macro" width="90" x="45" y="30">
            <parameter key="macro" value="table"/>
            <parameter key="macro_type" value="data_value"/>
            <parameter key="attribute_name" value="tbl_name"/>
            <parameter key="example_index" value="%{example}"/>
          </operator>
          <operator activated="true" class="read_database" expanded="true" height="60" name="Read Database (2)" width="90" x="179" y="75">

            <parameter key="query" value="SELECT * FROM `%{table}`">

	      </operator>
          <connect from_port="example set" to_op="Extract Macro" to_port="example set"/>
          <connect from_op="Extract Macro" from_port="example set" to_port="example set"/>
          <connect from_op="Read Database (2)" from_port="output" to_port="output 1"/>
          <portSpacing port="source_example set" spacing="0"/>
          <portSpacing port="sink_example set" spacing="0"/>
          <portSpacing port="sink_output 1" spacing="0"/>
          <portSpacing port="sink_output 2" spacing="0"/>
        </process>
      </operator>
      <connect from_op="Read Database" from_port="output" to_op="Loop Examples" to_port="example set"/>
      <connect from_op="Loop Examples" from_port="example 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"/>
    </process>
  </operator>
</process>

Hope that gives you a hint,
Tobias