Return-Path: 
 <cocoon-users-return-30815-apmail-xml-cocoon-users-archive=xml.apache.org@xml.apache.org>
Delivered-To: apmail-xml-cocoon-users-archive@xml.apache.org
Received: (qmail 73936 invoked by uid 500); 25 Mar 2002 16:22:09 -0000
Mailing-List: contact cocoon-users-help@xml.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:cocoon-users-help@xml.apache.org>
list-unsubscribe: <mailto:cocoon-users-unsubscribe@xml.apache.org>
list-post: <mailto:cocoon-users@xml.apache.org>
Reply-To: cocoon-users@xml.apache.org
Delivered-To: mailing list cocoon-users@xml.apache.org
Received: (qmail 73925 invoked from network); 25 Mar 2002 16:22:08 -0000
X-MimeOLE: Produced By Microsoft Exchange V6.0.4712.0
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Cocoon2: couldn't make nested queries work for ESQL in XSP.
Date: Mon, 25 Mar 2002 11:16:55 -0500
Message-ID: <937B9574F4A2A344812CB180F5B5D25E18A7EF@corpmail.eduprise.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Cocoon2: couldn't make nested queries work for ESQL in XSP.
Thread-Index: AcHUEc6LQXDyNyUgSFCvSfGKEBNNSQABoudA
From: "Marty McClelland" <mmcclelland@eduprise.com>
To: <cocoon-users@xml.apache.org>
X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N

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 =3D
Collection_resources.rid
WHERE     (Collection_resources.Collection_id =3D 18)
-->
   <esql:query>select Distinct Contribute.Centity_id, Contribute.role
FROM         Contribute , Collection_resources where
Collection_resources.Collection_id=3D<xsp:expr>collectionID</xsp:expr> =
and
Collection_resources.rid=3DContribute.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=3D"1"/></centityID>
<role><esql:get-string column=3D"2"/></role>
=20
 <!-- get the count -->
  <esql:execute-query>
    <esql:query>select count(distinct Contribute.rid) from Contribute,
Collection_resources where centity_id=3D<esql:parameter
type=3D"int"><esql:get-int ancestor=3D"1" column=3D"1" =
/></esql:parameter> and
Contribute.rid=3DCollection_resources.rid and
Collection_resources.Collection_id=3D<xsp:expr>collectionID</xsp:expr>
</esql:query>
    <esql:results>
      <esql:row-results>
          <count><esql:get-int column=3D"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=3D<esql:parameter type=3D"int"><esql:get-int ancestor=3D"1"
column=3D"1" /></esql:parameter></esql:query>
    <esql:results>
      <esql:row-results>
          <name><esql:get-string column=3D"2"/>, <esql:get-string
column=3D"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:Argyn.Kuketayev@plateau.com]=20
> Sent: Monday, March 25, 2002 10:34 AM
> To: 'cocoon-users@xml.apache.org'
> Subject: Cocoon2: couldn't make nested queries work for ESQL in XSP.
>=20
>=20
> I've to make a report on the table M, then for each row I=20
> need to show corresponding rows from tables F and C.
>=20
> Something like this:
>=20
> <rowset name=3D"M">
>   <row>
>     <ID>1</ID>
>=20
>     <rowset name=3D"F">   =20
>       <row>
>         <f_name>f1</f_name>
>       <row>
>       ...
>     </rowset>
>=20
>     <rowset name=3D"C">   =20
>       <row>
>         <c_name>c1</c_name>
>       <row>
>       ....
>     </rowset>
>   </row>
> ...
> </rowset>
>=20
> I tried this :
>=20
>  <esq
> l:parameter type=3D"int"><esql:get-int ancestor=3D"1"=20
> column=3D"id"/></esql:parameter>
>=20
> It didn't work with an exception:=20
>=20
> org.apache.cocoon.ProcessingException: Exception in
> ServerPagesGenerator.generate(): java.lang.RuntimeException:=20
> Error executing prepared statement: select ... from f where M_ID =3D ? =
:
> java.sql.SQLException: ORA-01722: invalid number
>=20
>=20
> Can somebody give me a WORKING and tested example of nested=20
> queries in Cocoon2?
>=20
> thanks,
> Argyn
>=20
> ---------------------------------------------------------------------
> Please check that your question has not already been answered=20
> in the FAQ before posting.=20
<http://xml.apache.org/cocoon/faqs.html>

To unsubscribe, e-mail: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>


---------------------------------------------------------------------
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: <cocoon-users-unsubscribe@xml.apache.org>
For additional commands, e-mail: <cocoon-users-help@xml.apache.org>