Thank you, Marty! I don't know what was the problem, I was doing almost the same. Now it works.
Argyn -----Original Message----- From: Marty McClelland [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 11:17 AM To: [EMAIL PROTECTED] Subject: RE: Cocoon2: couldn't make nested queries work for ESQL in XSP. 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]> --------------------------------------------------------------------- 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]>
