cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Pratt <Chris.Pr...@three.co.uk>
Subject RE: nested sql
Date Tue, 08 Apr 2003 14:46:43 GMT
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


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"/>
</esql:query>
<esql:results>
<esql:row-results>
<td>
<esql:get-int column="successes"/>
</td>	
</esql:row-results>
</esql:results>
</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>
</table>
</div>
</content>
</page>
</xsp:page>



-----Original Message-----
From: Christian Haul [mailto:haul@dvs1.informatik.tu-darmstadt.de]
Sent: 08 April 2003 15:25
To: cocoon-dev@xml.apache.org
Subject: Re: nested sql


On 08.Apr.2003 -- 03:18 PM, Chris Pratt wrote:
> Still having issues with nested sql
> 
> Does </esql:results> close all the result sets?
> 
> If i have 4 queries one after another how do I do this?

The esql sample contains multiple queries.

If it doesn't help -- please provide us with your code and be specific
about the issues you face. I'm sure this will be resolved in no time
at all.

	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


________________________________________________________________________

This e-mail message (including any attachment) is intended only for the personal 
use of the recipient(s) named above. This message is confidential and may be 
legally privileged.  If you are not an intended recipient, you may not review, copy or 
distribute this message. If you have received this communication in error, please notify 
us immediately by e-mail and delete the original message.

Any views or opinions expressed in this message are those of the author only. 
Furthermore, this message (including any attachment) does not create any legally 
binding rights or obligations whatsoever, which may only be created by the exchange 
of hard copy documents signed by a duly authorised representative of Hutchison 
3G UK Limited.
________________________________________________________________________


Mime
View raw message