You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Patrick Prodhon <pa...@sap.ap-hop-paris.fr> on 2003/05/20 15:00:43 UTC

Using max with OQL or criterias

Hello,


I need to translate some SQL query to OJB (otherwise, I'll have to use 
some SQL instruction directly, which is what we'd like to avoid).
Basically :
SELECT T.ID, T.SOME_DATE, T.OTHER_FIELDS FROM TAB T WHERE T.SOMEDATE = 
(SELECT MAX(SOMEDATE) FROM TAB WHERE ID=T.ID)

Any way I can achieve this either with OQL or criterias ? I've been 
looking for 'max' through the mail archive, but to no avail.

Thanks.
Patrick.

Re: Using max with OQL or criterias

Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi patrick,

i forgot to mention that the report query is NOT extent aware !
the class Article  has several subclasses that are not used to calculate 
the average.

jakob

Jakob Braeuchi wrote:

> hi patrick,
>
> imo you better youse criteria to solve this problem:
>
>        ReportQueryByCriteria subQuery;
>        Criteria subCrit = new Criteria();
>        Criteria crit = new Criteria();
>
>        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
>        subQuery.setColumns(new String[]{"avg(price)"});
>
>        crit.addGreaterOrEqualThan("price", subQuery);
>        Query q = QueryFactory.newQuery(Article.class, crit);
>
>        Collection results = broker.getCollectionByQuery(q);
>
> this query return all articles with a price above average.
>
> SELECT 
> A0.Einzelpreis,A0.Kategorie_Nr,A0.Auslaufartikel,A0.MindestBestand,A0.Lagerbestand,A0.Artikel_Nr,A0.Liefereinheit,A0.BestellteEinheiten,A0.Lieferanten_Nr,A0.Artikelname 
> FROM Artikel A0 WHERE A0.Einzelpreis >=  (SELECT avg(A0.Einzelpreis) 
> FROM Artikel A0)
>
>
> hth
> jakob
>
> Patrick Prodhon wrote:
>
>> Hello,
>>
>>
>> I need to translate some SQL query to OJB (otherwise, I'll have to 
>> use some SQL instruction directly, which is what we'd like to avoid).
>> Basically :
>> SELECT T.ID, T.SOME_DATE, T.OTHER_FIELDS FROM TAB T WHERE T.SOMEDATE 
>> = (SELECT MAX(SOMEDATE) FROM TAB WHERE ID=T.ID)
>>
>> Any way I can achieve this either with OQL or criterias ? I've been 
>> looking for 'max' through the mail archive, but to no avail.
>>
>> Thanks.
>> Patrick.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>

Re: Using max with OQL or criterias

Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi patrick,

imo you better youse criteria to solve this problem:

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setColumns(new String[]{"avg(price)"});

        crit.addGreaterOrEqualThan("price", subQuery);
        Query q = QueryFactory.newQuery(Article.class, crit);

        Collection results = broker.getCollectionByQuery(q);

this query return all articles with a price above average.

SELECT 
A0.Einzelpreis,A0.Kategorie_Nr,A0.Auslaufartikel,A0.MindestBestand,A0.Lagerbestand,A0.Artikel_Nr,A0.Liefereinheit,A0.BestellteEinheiten,A0.Lieferanten_Nr,A0.Artikelname 
FROM Artikel A0 WHERE A0.Einzelpreis >=  (SELECT avg(A0.Einzelpreis) 
FROM Artikel A0)


hth
jakob

Patrick Prodhon wrote:

> Hello,
>
>
> I need to translate some SQL query to OJB (otherwise, I'll have to use 
> some SQL instruction directly, which is what we'd like to avoid).
> Basically :
> SELECT T.ID, T.SOME_DATE, T.OTHER_FIELDS FROM TAB T WHERE T.SOMEDATE = 
> (SELECT MAX(SOMEDATE) FROM TAB WHERE ID=T.ID)
>
> Any way I can achieve this either with OQL or criterias ? I've been 
> looking for 'max' through the mail archive, but to no avail.
>
> Thanks.
> Patrick.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>