Options

building up dynamic SQL queries

rschillingrschilling Member Posts: 1 Contributor I
edited November 2018 in Help
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

Answers

  • Options
    TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    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
Sign In or Register to comment.