I've done a number of nested queries in C1.8.2 and as far as I know the
approach is the same in C2. Here is an example:
<esql:execute-query>
<!-- for each rid in Collection - get the centity's -->
<!-- SQL Server query
SELECT DISTINCT Contribute.centity_id, Contribute.role
FROM Contribute INNER JOIN
Collection_resources ON Contribute.rid =
Collection_resources.rid
WHERE (Collection_resources.Collection_id = 18)
-->
<esql:query>select Distinct Contribute.Centity_id, Contribute.role
FROM Contribute , Collection_resources where
Collection_resources.Collection_id=<xsp:expr>collectionID</xsp:expr> and
Collection_resources.rid=Contribute.rid</esql:query>
<esql:error-results>
<!-- error-results occur when there is a sql error -->
<!-- db connection errors are thrown to xsp -->
<!-- stacktrace is shown for errors in xsp -->
<error>SQL error - contact the ilumina
administrator</error>
</esql:error-results>
<esql:results>
<resources>
<esql:row-results>
<resource>
<centityID><esql:get-int column="1"/></centityID>
<role><esql:get-string column="2"/></role>
<!-- get the count -->
<esql:execute-query>
<esql:query>select count(distinct Contribute.rid) from Contribute,
Collection_resources where centity_id=<esql:parameter
type="int"><esql:get-int ancestor="1" column="1" /></esql:parameter> and
Contribute.rid=Collection_resources.rid and
Collection_resources.Collection_id=<xsp:expr>collectionID</xsp:expr>
</esql:query>
<esql:results>
<esql:row-results>
<count><esql:get-int column="1"/></count>
</esql:row-results>
</esql:results>
</esql:execute-query>
<!-- get the name -->
<esql:execute-query>
<esql:query>select first_name, last_name from Centity where
centity_id=<esql:parameter type="int"><esql:get-int ancestor="1"
column="1" /></esql:parameter></esql:query>
<esql:results>
<esql:row-results>
<name><esql:get-string column="2"/>, <esql:get-string
column="1"/></name>
</esql:row-results>
</esql:results>
</esql:execute-query>
</resource>
</esql:row-results>
</resources>
</esql:results>
</esql:execute-query>
marty
> -----Original Message-----
> From: Argyn Kuketayev [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 25, 2002 10:34 AM
> To: '[EMAIL PROTECTED]'
> Subject: Cocoon2: couldn't make nested queries work for ESQL in XSP.
>
>
> I've to make a report on the table M, then for each row I
> need to show corresponding rows from tables F and C.
>
> Something like this:
>
> <rowset name="M">
> <row>
> <ID>1</ID>
>
> <rowset name="F">
> <row>
> <f_name>f1</f_name>
> <row>
> ...
> </rowset>
>
> <rowset name="C">
> <row>
> <c_name>c1</c_name>
> <row>
> ....
> </rowset>
> </row>
> ...
> </rowset>
>
> I tried this :
>
> <esq
> l:parameter type="int"><esql:get-int ancestor="1"
> column="id"/></esql:parameter>
>
> It didn't work with an exception:
>
> org.apache.cocoon.ProcessingException: Exception in
> ServerPagesGenerator.generate(): java.lang.RuntimeException:
> Error executing prepared statement: select ... from f where M_ID = ? :
> java.sql.SQLException: ORA-01722: invalid number
>
>
> Can somebody give me a WORKING and tested example of nested
> queries in Cocoon2?
>
> thanks,
> Argyn
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered
> in the FAQ before posting.
<http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail: <[EMAIL PROTECTED]>
