cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Haul <h...@dvs1.informatik.tu-darmstadt.de>
Subject Re: nested sql
Date Tue, 08 Apr 2003 15:25:55 GMT
On 08.Apr.2003 -- 03:46 PM, Chris Pratt wrote:
> You asked for it,(sorry) below is the code:
> 
> Basically what I want to do is use the id from the first qurery in the 
> second, third and fourth qurery.
> 
> But I want the results to be returned into the same table <td> for each
> result.
> 
> In jsp I would name a result set for each and loop through the first query,
> get a result set for each sub query and then put it in the table data. (Does
> that make sence)
> 
> 
> I have tried closing the row-result and result at end and using
> ancestor="1", ancestor="2", ancestor="3" etc. still no luck

Maybe it's a problem with your DBMS and / or JDBC driver. You could
try to open a new connection for each nested query.

I've indented your code, see comments inline (please send proper
formated XML to the list only -- it's very difficult to read otherwise):

<esql:connection>
 <esql:pool>chris</esql:pool>
 <esql:execute-query>
  <esql:query name="pipeline">
   SELECT id, name FROM content_format ORDER BY name
  </esql:query>
  <esql:results>
   <esql:row-results>
    <tr>
     <td>
      <esql:get-int column="name"/>
     </td>  
     <esql:execute-query>
      <esql:query name="successes">
       SELECT count(cfeed.id) successes
       FROM stage,
       component_feedback cfeed,
       component comp
       WHERE stage.id = cfeed.stage_id
       AND cfeed.status = 'S'
       AND comp.id = stage.comp_id
       AND comp.jndi_name = 'contentpersistorinitiator'
       AND cfeed.eventtime > (sysdate - 0.5)
       AND stage.cont_id = <esql:get-int column="id" ancestor="1"/>
<!--
  although the above should work, I'd strongly recommend to use
  <esql:parameter type="int"><esql:get-int column="id" ancestor="1"/></esql:parameter>
  instead.
-->
      </esql:query>
      <esql:results>
       <esql:row-results>
        <td>
         <esql:get-int column="successes"/>
        </td>   
       </esql:row-results>
      </esql:results>
<!-- bogus code: 
     </td>  
    </esql:row-results>
   </esql:results>
--> 
     </esql:execute-query>     
     <esql:execute-query>
      <esql:query name="errors">
       SELECT count(cf.id) errors
       FROM stage,
       component_feedback cf
       WHERE stage.id = cf.stage_id
       AND cf.status = 'F'
       AND cf.eventtime > (sysdate - 0.5)
       AND stage.cont_id  = <esql:get-int column="cont_id" ancestor="1"/>
      </esql:query>
      <esql:results>
       <esql:row-results>
        <td>
         <esql:get-int column="errors"/>
        </td>
       </esql:row-results> 
      </esql:results>
     </esql:execute-query>   
     <esql:execute-query>
      <esql:query name="last-delivered">
       SELECT to_char(eventtime, 'DD-YY-YYYY HH24:MI:SS') 'last-delivered'
       FROM
       (SELECT max(cf.eventtime) eventtime
       FROM stage,
       component_feedback cf,
       component comp
       WHERE cf.stage_id = stage.id
       AND stage.comp_id = comp.id
       AND comp.jndi_name = 'rdfvalidatingparser'
       AND stage.cont_id = <esql:get-int column="id" ancestor="1"/>
      </esql:query>
      <esql:results>
       <esql:row-results>
        <td>
         <esql:get-ascii column="last-delivered"/>
        </td>
       </esql:row-results>
      </esql:results>
     </esql:execute-query>
     <esql:execute-query>
      <esql:query name="averages">
       SELECT round(avg(ingested.eventtime - delivered.eventtime) * 24 * 60 * 60)
       AS average
       FROM component_feedback ingested,
       component_feedback delivered,
       stage,
       component comp
       WHERE stage.cont_id = <esql:get-int coumn="id" ancestor="1"/>
       AND stage.comp_id = comp.id
       AND comp.jndi_name = 'contentpersistorinitiator'
       AND stage.id = ingested.stage_id
       AND ingested.status = 'S'
       AND delivered.stage_id = 0
       AND ingested.ingest_id = delivered.ingest_id
       AND delivered.eventtime > (sysdate - 0.5)
      </esql:query>
      <esql:results>
       <esql:row-results>
        <td>
         <esql:get-int column="average"/>
        </td>
       </esql:row-results>
      </esql:results>
     </esql:execute-query>
    </tr>
   </esql:row-results>
  </esql:results>
 </esql:execute-query>
</esql:connection>

	Chris.
-- 
C h r i s t i a n       H a u l
haul@informatik.tu-darmstadt.de
    fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

Mime
View raw message