Re: Count a Subquery with group clause
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { lastname, age, city, count(*) });
subQuery.addGroupBy(new String[] { lastname, age, city });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao [EMAIL PROTECTED] wrote:
Hello All,
How can I generate the following SQL statement using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Sorry,
Read this:
subQuery.addGroupBy(new String[] { lastname, age, city });
as this:
query.addGroupBy(new String[] { lastname, age, city });
Cheers,
Vasily
On 8/9/06, Vasily Ivanov [EMAIL PROTECTED] wrote:
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { lastname, age, city, count(*) });
subQuery.addGroupBy(new String[] { lastname, age, city });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao [EMAIL PROTECTED] wrote:
Hello All,
How can I generate the following SQL statement using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Sorry again, :)
Your query will run, but it will not make any sence. It is the same as write:
SELECT count(*) FROM
FROM people
Cheers,
Vasily
On 8/9/06, Vasily Ivanov [EMAIL PROTECTED] wrote:
Hi Bin,
Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { lastname, age, city, count(*) });
subQuery.addGroupBy(new String[] { lastname, age, city });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao [EMAIL PROTECTED] wrote:
Hello All,
How can I generate the following SQL statement using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Count a Subquery with group clause
Thanks, Vasily.
I think your OJB code may return different results
from the SQL statement. Your OJB code will return a
number for each people group. But by the SQL statement
, we can only get a number, the count of groups.
Anyway, you gave me a great suggestion. Thanks again,
Bin
--- Vasily Ivanov [EMAIL PROTECTED] wrote:
Sorry,
Read this:
subQuery.addGroupBy(new String[] { lastname, age,
city });
as this:
query.addGroupBy(new String[] { lastname, age,
city });
Cheers,
Vasily
On 8/9/06, Vasily Ivanov [EMAIL PROTECTED] wrote:
Hi Bin,
Your query will not run because you can not put in
GROUP BY without
count(*) in SELECT clause of subquery. If you want
to run this:
=
SELECT lastname, age, city, count(*)
FROM people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new
Criteria());
query.setAttributes(new String[] { lastname, age,
city, count(*) });
subQuery.addGroupBy(new String[] { lastname, age,
city });
broker.getReportQueryIteratorByQuery(query);
=
Cheers,
Vasily
On 8/9/06, Bin Gao [EMAIL PROTECTED] wrote:
Hello All,
How can I generate the following SQL statement
using
OJB?
SELECT count(*) FROM (
SELECT lastname, age, city
FROM people
GROUP BY lastname, age, city
);
Thanks for your help,
Bin
-
To unsubscribe, e-mail:
[EMAIL PROTECTED]
For additional commands, e-mail:
[EMAIL PROTECTED]
-
To unsubscribe, e-mail:
[EMAIL PROTECTED]
For additional commands, e-mail:
[EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Methods of calling stored procedures in OJB
Ron, My doubt was how was the stored procedure being called? I was thinking that you had to actually call it in your java code. However, I think what you and the documentation are implicitly saying is that the broker is actually calling the stored procedure as you have configured it in the repository. Therefore there is no need to call the stored procedure in one's java code. When I pass an object defined to use a stored procedure to the OJB store instruction the s.p. will be executed. Does this sound correct? Thanks, Jim Wright -Original Message- From: Ron Gallagher [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 08, 2006 8:44 PM To: OJB Users List Subject: Re: Methods of calling stored procedures in OJB Jim -- OJB supports the use of stored procedures to handle all persistence operations (insert, update and delete). The stored procedures that are configured in the repository are used in place of standard insert, update and delete SQL commands. You do not need to use any sort of database triggers in order to take advantage of OJB's use of stored procedures for persistence operations. The example that you refer in the documentation to was culled from an application that I was one of the lead architects on. In the environment that that application was to be deployed in, the DBAs insisted that all persistence operations be performed by stored procedures. This was accomplished by utilizing OJB's support for stored procedures. In that same environment, the business community insisted that we track who created and performed the last update on each record. That requirement was accomplished via the insert/update triggers that are referenced in the sample. The final feature was the requirement that any updates to the four audit columns be reflected in the java objects that were managed by the application. This requirement was accomplished in two steps: (1) the stored procedures that OJB utilized returned these values to the caller (i.e. OJB) via the RETURNING clause and (2) the configuration of the stored procedure in the repository file included the attri bute 'return=true' attribute on any arguments that were returned to OJB. I hope this helps you out. Let me know if I can clarify things any further. Ron Gallagher From: Wright, Jim \(NIH/CIT\) [C] [EMAIL PROTECTED] Date: 2006/08/08 Tue PM 04:57:32 EDT To: ojb-user@db.apache.org Subject: Methods of calling stored procedures in OJB Does one have to use database triggers in order to run stored procedures in OJB? The Apache documentation seems to imply that: http://db.apache.org/ojb/docu/howtos/howto-work-with-stored-procedures.h tml#A+simple+example I need a way to run them that does not involve triggers. Jim Wright - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Methods of calling stored procedures in OJB
Jim -- Your assumptions are absolutely correct. Ron Gallagher From: Wright, Jim \(NIH/CIT\) [C] [EMAIL PROTECTED] Date: 2006/08/09 Wed AM 11:09:41 EDT To: OJB Users List ojb-user@db.apache.org Subject: RE: Methods of calling stored procedures in OJB Ron, My doubt was how was the stored procedure being called? I was thinking that you had to actually call it in your java code. However, I think what you and the documentation are implicitly saying is that the broker is actually calling the stored procedure as you have configured it in the repository. Therefore there is no need to call the stored procedure in one's java code. When I pass an object defined to use a stored procedure to the OJB store instruction the s.p. will be executed. Does this sound correct? Thanks, Jim Wright -Original Message- From: Ron Gallagher [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 08, 2006 8:44 PM To: OJB Users List Subject: Re: Methods of calling stored procedures in OJB Jim -- OJB supports the use of stored procedures to handle all persistence operations (insert, update and delete). The stored procedures that are configured in the repository are used in place of standard insert, update and delete SQL commands. You do not need to use any sort of database triggers in order to take advantage of OJB's use of stored procedures for persistence operations. The example that you refer in the documentation to was culled from an application that I was one of the lead architects on. In the environment that that application was to be deployed in, the DBAs insisted that all persistence operations be performed by stored procedures. This was accomplished by utilizing OJB's support for stored procedures. In that same environment, the business community insisted that we track who created and performed the last update on each record. That requirement was accomplished via the insert/update triggers that are referenced in the sample. The final feature was the requirement that any updates to the four audit columns be reflected in the java objects that were managed by the application. This requirement was accomplished in two steps: (1) the stored procedures that OJB utilized returned these values to the caller (i.e. OJB) via the RETURNING clause and (2) the configuration of the stored procedure in the repository file included the attri bute 'return=true' attribute on any arguments that were returned to OJB. I hope this helps you out. Let me know if I can clarify things any further. Ron Gallagher From: Wright, Jim \(NIH/CIT\) [C] [EMAIL PROTECTED] Date: 2006/08/08 Tue PM 04:57:32 EDT To: ojb-user@db.apache.org Subject: Methods of calling stored procedures in OJB Does one have to use database triggers in order to run stored procedures in OJB? The Apache documentation seems to imply that: http://db.apache.org/ojb/docu/howtos/howto-work-with-stored-procedures.h tml#A+simple+example I need a way to run them that does not involve triggers. Jim Wright - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
