Query inputs using 'Set Macros'

spoorti

I have a query which requires more than one input parameters. As an example
                Select count(*) from TestTable where date  > '2012/10/05' and date < '2012/10/10'
I would like to pass the date parameter as variable . So my query will look like
                Select count(*) from TestTable where date  > ? and date < ?
How do I pass 2 variables to the 'Read Database Operator' ? I tried usibg 'Set Macros' and defined 'dt1' and 'dt2' and used them in 'Read Database' with 'prepare statement' and adding VARCHAR {%dt1} and {%dt2} to the Edit Enumeration.

I get the error
ERROR: invalid input syntax for type date: "dt1"

Is there different way to pass more than one variables to sql statement ? It works fine if I have one variable . E.g
Select Count(*) from TestTable where date = ?
And define input variable with 'Set Macro'.

spoorti

Any help on this ? Is there any other way to achieve this ?
Thanks.

Skirzynski

The correct syntax for a macro is %{dt1} and not {%dt1}. Please check your syntax in the enumeration. If this does not help and was just a typo in your posting: Build a minimal example and post it (do not forget the code-tags) so i can take a look.

  Marcin

spoorti

Here is XML  for the process I have created

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.2.008">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.2.008" expanded="true" name="Process">
    <process expanded="true" height="670" width="727">
      <operator activated="true" class="read_database" compatibility="5.2.008" expanded="true" height="60" name="Read Database" width="90" x="179" y="30">
        <parameter key="connection" value="Vertica"/>
        <parameter key="query" value="( Select timestamp &#10;from TestTable&#10;where DATE(TO_TIMESTAMP( timestamp) )  = cast ( ? as date) )&#10;UNION ALL&#10;( Select timestamp &#10;from TestTable&#10;where DATE(TO_TIMESTAMP( timestamp) )  = cast ( ? as date));"/>   
        <parameter key="table_name" value="dimlead"/>
        <parameter key="prepare_statement" value="true"/>
        <enumeration key="parameters">
          <parameter key="parameter" value="VARCHAR.%{dt1}"/>
          <parameter key="parameter" value="VARCHAR.%{dt2}"/>
        </enumeration>
      </operator>
      <operator activated="true" class="set_macros" compatibility="5.2.008" expanded="true" height="76" name="Set Macros" width="90" x="45" y="120">
        <list key="macros">
          <parameter key="dt1" value="2012/06/17"/>
          <parameter key="dt2" value="2012/06/18"/>
        </list>
      </operator>
      <connect from_port="input 1" to_op="Set Macros" to_port="through 1"/>
      <connect from_op="Read Database" from_port="output" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="source_input 2" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>