modify SQL String

sissi pol
  • sissi pol

    sissi pol - 2003-10-29

    Hello all,

    is it possible to modify the SQL-String? I can't tell the report to select with paramters, because these parameters aren' t always the same. And so the WHERE-Clause differs every time.

    I tried it with one Parameter (String) who should be the WHERE-Clause but the following SQL-String:

    select T_log1.Tll_id, T_log1.kat, T_log1.Zusdat, T_log1.Pos_id, T_log1.Emp_id, T_log1.Zeit
    from T_log1
    where (  {?Select})
    order by T_log1.Tll_id asc, T_log1.Pos_id asc, T_log1.Emp_id asc, T_log1.kat asc, T_log1.Zeit asc

    doesn't work!

    Can anyone help me?

    • Jim Menard

      Jim Menard - 2003-10-29

      Why doesn't it work? What did you enter as the value of the Select parameter?

    • sissi pol

      sissi pol - 2003-10-30

      Sorry i forgot it!

      {?Select} :
      T_log.Pos_id > 0 AND Tlog.Emp_id = 23 AND T_log1.kat = 0

      Does it only works with { }?

    • sissi pol

      sissi pol - 2003-10-30

      okay, i also tried with { }, but no effect!!

      To simplifie i tried it with

      {T_log1.Pos_id} > 0

      Can anyone help me?

      • Jim Menard

        Jim Menard - 2003-11-26

        Column names can't appear in parameters used in WHERE clauses. That's because the entire parameter is turned into a string by the Java JDBC code. Your query becomes

        SELECT ... FROM ...
        WHERE '{T_log1.Pos_id} > 0'

        I would like to figure out a way to allow this in DataVision.

    • Anonymous - 2003-12-03

      to dynamically generate whereCluase query , my solution
      is to split .xml file into 3 parts:
      1. first  part: header.xml file it includes the static select part like this:
      <?xml version="1.0" encoding="UTF-8"?>
      <!-- Generated by DataVision version 0.7.13 -->
      <!-- -->
      <report dtd-version="0.8" name="Unnamed" title="Untitled" author="">
          <paper name="US-Letter" orientation="portrait"/>
          <database driverClassName="oracle.jdbc.driver.OracleDriver" connInfo="jdbc:oracle:thin:@... " name="" username=""/>
              <join from="....." relation="=" to="...."/>
              <join from="..." relation="=" to="...."/>
      ends here.
      2. the  2nd part comes from my own whereClause string which is dynamically generated in java code.
      // here insert the complete whereclause as a string
      String whereClauseStr=" tablea.year=" +thisYear
      +" and tablea.week="+thisWeek+.....;

      3. the last part is tail.xml
      like this:
          <parameter id="11" type="string" name="..." question="...=" arity="single">
          <section height="35.0">
              <field id="1" type="text">
              <text><![CDATA[ Project Report]]></text>
              <bounds x="76.0" y="17.0" width="152.0" height="16.0"/>
              <format size="12.0" bold="true" align="center" color="102;0;255;255"/>
          <section height="18.0"/>
              <section height="44.0">
              <field id="3" type="text">
                  <text><![CDATA[User Name]]></text>
                  <bounds x="417.0" y="19.0" width="58.0" height="17.0"/>
          <section height="46.0">

      so when you read from xml file, actually you first read from header.xml and put it into a stringbuffer, then your whereclause string to the stringbuffer , then the last part tail.xml ...
      after all, you may convert the whole complete string into
      byte array and use:
      ByteArrayInputStream streamArray =
                      new ByteArrayInputStream(byteArray);

                  InputStreamReader inputReader =
                      new InputStreamReader(streamArray);
      then you may generate your report as usual.

      hope it will help ..

    • sissi pol

      sissi pol - 2003-12-04

      Thank you very much, but it works in also in a simpler way:

      report.getDataSource().getQuery().setEditableWhereClause(String whereclause);


Log in to post a comment.