Re: HAVING clause
hi david,
because there's only one having in a query. when adding having to the
criteria it would be possible to have multiple havings.
the same is also true for groupby / orderby which are currently located
at the criteria.
and it's imo not very intuitive to add a having criteria to a
where-criteria.
jakob
David Mitchell wrote:
Cool! Just curious, can you elaborate a little as to why you felt that the
Having clause should go in query as opposed to in criteria?
-dave
-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2003 2:43 PM
To: OJB Users List
Subject: Re: HAVING clause
hi all,
i just commited the first try to support HAVING clause.
this sample code with a having-criteria:
ReportQueryByCriteria query;
Criteria crit, having;
crit = new Criteria();
crit.addGroupBy(new String[] { "id", "name", "vorname" });
having = new Criteria();
having.addGreaterThan("sum(konti.saldo)", new Integer(200));
query = new ReportQueryByCriteria(Person.class, crit);
query.setColumns(new String[] { "id", "name", "vorname",
"sum(konti.saldo)" });
query.setHavingCriteria(having);<<<<
broker.getReportQueryIteratorByQuery(query);;
produces this sql:
SELECT A0.id,A0.name,A0.vorname,sum(A1.saldo) FROM tabPerson A0 INNER
JOIN tabKonto A1 ON A0.id=A1.idPerson GROUP BY A0.id,A0.name,A0.vorname
HAVING sum(A1.saldo) > '200'
do not expect this first version to work perfectly ! there will be
problems when the whereCriteria is empty but the havingCriteria not.
hth
jakob
David Mitchell wrote:
Is there some particular reason why there's no "addHaving()" method on
Criteria?
I needed to do the following query on my database. (I am implementing an
INTERSECTION query, which isnt supported by my database, SQL Server)
SELECT numOperators
FROM flFactoryOperatorCycleTime
WHERE (workCenterNdx = 64) AND (productNdx IN (753, 754, 758))
GROUP BY numOperators
HAVING (COUNT(*) = 3)
Since I needed to only query a single field (numOperators) and could not
deal with selecting ALL columns (due to the group by clause), I used a
report query . So far so good. Then, I ran into trouble because report
queries can only be created from Criteria, which don't seem to support
"HAVING" clauses.
My hack (which works) to get around it is to FAKE it! But I do wish I had
a
better solution.
Criteria crit = new Criteria();
crit.addEqualTo(IFactoryOperatorCycleTime.WORKCENTERNDX, new
Integer(((IPersistentObject)workCenter).getNdx()));
crit.addIn(IFactoryOperatorCycleTime.PRODUCTNDX, productNdxs);
crit.addSql("(1=1) GROUP BY NUMOPERATORS HAVING (COUNT(*) =
"+productNdxs.size()+")");
The reason for the "(1=1)" is because when you do an addSql() on a
criteria, it helpfully puts in the 'AND' part for you, but "AND HAVING..."
doesnt work,
and HAVING has to come after the GROUP BY, so if I do the addGroupBy()
method separately, the HAVING gets put BEFORE the GROUP BY, which also
doesnt work.
The final query ends up looking like this:
SELECT A0.numOperators FROM flFactoryOperatorCycleTime A0 WHERE ((
A0.workCenterNdx = ? ) AND
(A0.productNdx IN ( ? , ? , ? ))) AND (1=1) GROUP BY NUMOPERATORS HAVING
(COUNT(*) = 3)
Any better suggestions?
thanks-
David Mitchell
+-+
This message may contain confidential and/or privileged information. If
you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or
any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message.
Thank you for your cooperation.
+-+
-
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]
+-+
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message.
Thank you for your cooperation.
+-+
-
RE: HAVING clause
Cool! Just curious, can you elaborate a little as to why you felt that the
Having clause should go in query as opposed to in criteria?
-dave
-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2003 2:43 PM
To: OJB Users List
Subject: Re: HAVING clause
hi all,
i just commited the first try to support HAVING clause.
this sample code with a having-criteria:
ReportQueryByCriteria query;
Criteria crit, having;
crit = new Criteria();
crit.addGroupBy(new String[] { "id", "name", "vorname" });
having = new Criteria();
having.addGreaterThan("sum(konti.saldo)", new Integer(200));
query = new ReportQueryByCriteria(Person.class, crit);
query.setColumns(new String[] { "id", "name", "vorname",
"sum(konti.saldo)" });
query.setHavingCriteria(having);<<<<
broker.getReportQueryIteratorByQuery(query);;
produces this sql:
SELECT A0.id,A0.name,A0.vorname,sum(A1.saldo) FROM tabPerson A0 INNER
JOIN tabKonto A1 ON A0.id=A1.idPerson GROUP BY A0.id,A0.name,A0.vorname
HAVING sum(A1.saldo) > '200'
do not expect this first version to work perfectly ! there will be
problems when the whereCriteria is empty but the havingCriteria not.
hth
jakob
David Mitchell wrote:
> Is there some particular reason why there's no "addHaving()" method on
>Criteria?
>
> I needed to do the following query on my database. (I am implementing an
>INTERSECTION query, which isnt supported by my database, SQL Server)
>
>SELECT numOperators
>FROM flFactoryOperatorCycleTime
>WHERE (workCenterNdx = 64) AND (productNdx IN (753, 754, 758))
>GROUP BY numOperators
>HAVING (COUNT(*) = 3)
>
> Since I needed to only query a single field (numOperators) and could not
>deal with selecting ALL columns (due to the group by clause), I used a
>report query . So far so good. Then, I ran into trouble because report
>queries can only be created from Criteria, which don't seem to support
>"HAVING" clauses.
>
> My hack (which works) to get around it is to FAKE it! But I do wish I had
a
>better solution.
>
>Criteria crit = new Criteria();
>crit.addEqualTo(IFactoryOperatorCycleTime.WORKCENTERNDX, new
>Integer(((IPersistentObject)workCenter).getNdx()));
>crit.addIn(IFactoryOperatorCycleTime.PRODUCTNDX, productNdxs);
>crit.addSql("(1=1) GROUP BY NUMOPERATORS HAVING (COUNT(*) =
>"+productNdxs.size()+")");
>
> The reason for the "(1=1)" is because when you do an addSql() on a
>criteria, it helpfully puts in the 'AND' part for you, but "AND HAVING..."
>doesnt work,
>and HAVING has to come after the GROUP BY, so if I do the addGroupBy()
>method separately, the HAVING gets put BEFORE the GROUP BY, which also
>doesnt work.
>
> The final query ends up looking like this:
>
>SELECT A0.numOperators FROM flFactoryOperatorCycleTime A0 WHERE ((
>A0.workCenterNdx = ? ) AND
>(A0.productNdx IN ( ? , ? , ? ))) AND (1=1) GROUP BY NUMOPERATORS HAVING
>(COUNT(*) = 3)
>
> Any better suggestions?
>
> thanks-
>David Mitchell
>
>
>
>
>
>
>
>+-+
>This message may contain confidential and/or privileged information. If
you
>are not the addressee or authorized to receive this for the addressee, you
>must not use, copy, disclose or take any action based on this message or
any
>information herein. If you have received this message in error, please
>advise the sender immediately by reply e-mail and delete this message.
>Thank you for your cooperation.
>+-+
>
>-
>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]
+-+
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message.
Thank you for your cooperation.
+-+
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: HAVING clause
hi all,
i just commited the first try to support HAVING clause.
this sample code with a having-criteria:
ReportQueryByCriteria query;
Criteria crit, having;
crit = new Criteria();
crit.addGroupBy(new String[] { "id", "name", "vorname" });
having = new Criteria();
having.addGreaterThan("sum(konti.saldo)", new Integer(200));
query = new ReportQueryByCriteria(Person.class, crit);
query.setColumns(new String[] { "id", "name", "vorname",
"sum(konti.saldo)" });
query.setHavingCriteria(having);
broker.getReportQueryIteratorByQuery(query);;
produces this sql:
SELECT A0.id,A0.name,A0.vorname,sum(A1.saldo) FROM tabPerson A0 INNER
JOIN tabKonto A1 ON A0.id=A1.idPerson GROUP BY A0.id,A0.name,A0.vorname
HAVING sum(A1.saldo) > '200'
do not expect this first version to work perfectly ! there will be
problems when the whereCriteria is empty but the havingCriteria not.
hth
jakob
David Mitchell wrote:
Is there some particular reason why there's no "addHaving()" method on
Criteria?
I needed to do the following query on my database. (I am implementing an
INTERSECTION query, which isnt supported by my database, SQL Server)
SELECT numOperators
FROM flFactoryOperatorCycleTime
WHERE (workCenterNdx = 64) AND (productNdx IN (753, 754, 758))
GROUP BY numOperators
HAVING (COUNT(*) = 3)
Since I needed to only query a single field (numOperators) and could not
deal with selecting ALL columns (due to the group by clause), I used a
report query . So far so good. Then, I ran into trouble because report
queries can only be created from Criteria, which don't seem to support
"HAVING" clauses.
My hack (which works) to get around it is to FAKE it! But I do wish I had a
better solution.
Criteria crit = new Criteria();
crit.addEqualTo(IFactoryOperatorCycleTime.WORKCENTERNDX, new
Integer(((IPersistentObject)workCenter).getNdx()));
crit.addIn(IFactoryOperatorCycleTime.PRODUCTNDX, productNdxs);
crit.addSql("(1=1) GROUP BY NUMOPERATORS HAVING (COUNT(*) =
"+productNdxs.size()+")");
The reason for the "(1=1)" is because when you do an addSql() on a
criteria, it helpfully puts in the 'AND' part for you, but "AND HAVING..."
doesnt work,
and HAVING has to come after the GROUP BY, so if I do the addGroupBy()
method separately, the HAVING gets put BEFORE the GROUP BY, which also
doesnt work.
The final query ends up looking like this:
SELECT A0.numOperators FROM flFactoryOperatorCycleTime A0 WHERE ((
A0.workCenterNdx = ? ) AND
(A0.productNdx IN ( ? , ? , ? ))) AND (1=1) GROUP BY NUMOPERATORS HAVING
(COUNT(*) = 3)
Any better suggestions?
thanks-
David Mitchell
+-+
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message.
Thank you for your cooperation.
+-+
-
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: HAVING clause
hi david,
there's no having clause in ojb so far.
imo it's not too difficult to implement, but i'm not sure whether it
belongs to Criteria or to Query.
Criteria#addHaving (Criteria aHavingCriteria) should be sufficient.
jakob
David Mitchell wrote:
Is there some particular reason why there's no "addHaving()" method on
Criteria?
I needed to do the following query on my database. (I am implementing an
INTERSECTION query, which isnt supported by my database, SQL Server)
SELECT numOperators
FROM flFactoryOperatorCycleTime
WHERE (workCenterNdx = 64) AND (productNdx IN (753, 754, 758))
GROUP BY numOperators
HAVING (COUNT(*) = 3)
Since I needed to only query a single field (numOperators) and could not
deal with selecting ALL columns (due to the group by clause), I used a
report query . So far so good. Then, I ran into trouble because report
queries can only be created from Criteria, which don't seem to support
"HAVING" clauses.
My hack (which works) to get around it is to FAKE it! But I do wish I had a
better solution.
Criteria crit = new Criteria();
crit.addEqualTo(IFactoryOperatorCycleTime.WORKCENTERNDX, new
Integer(((IPersistentObject)workCenter).getNdx()));
crit.addIn(IFactoryOperatorCycleTime.PRODUCTNDX, productNdxs);
crit.addSql("(1=1) GROUP BY NUMOPERATORS HAVING (COUNT(*) =
"+productNdxs.size()+")");
The reason for the "(1=1)" is because when you do an addSql() on a
criteria, it helpfully puts in the 'AND' part for you, but "AND HAVING..."
doesnt work,
and HAVING has to come after the GROUP BY, so if I do the addGroupBy()
method separately, the HAVING gets put BEFORE the GROUP BY, which also
doesnt work.
The final query ends up looking like this:
SELECT A0.numOperators FROM flFactoryOperatorCycleTime A0 WHERE ((
A0.workCenterNdx = ? ) AND
(A0.productNdx IN ( ? , ? , ? ))) AND (1=1) GROUP BY NUMOPERATORS HAVING
(COUNT(*) = 3)
Any better suggestions?
thanks-
David Mitchell
+-+
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message.
Thank you for your cooperation.
+-+
-
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: Having Clause with OJB
hi reinhard, there's no having clause in ojb so far. jakob Reinhard Dunst wrote: Hi, how can I realize this SQL statement with OJB select company_id from companyattributes where attribute_id in (35,16,24) group by company_id having count(company_id) = 3 does anybody have an idea thanks in advance Reinhard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
