db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: ReportQueryByCriteria do not resolve name of field in the SQL
Date Sun, 13 Feb 2005 10:22:25 GMT
hi antonio, carlos,

i committed some enhancements to support expression in attributes ie: 
sum(0.9 * price * stock). be aware that all attributes contained in the 
expressions have to belong to the same table !

please have a look at it.

jakob

Antonio Gallardo schrieb:
> Hi Jakob!
> 
> Thank you very much for the help. I thought this is a good start to try to
> fix it. As was told, we need to cover all the posible expressions. Carlos
> discovered this issue because he was trying to use this kind of
> expressions in a ReportQuery:
> 
> COALESCE(price,0)+COALESCE(bonus,0)
> 
> In any case is good that OJB team is now aware of this limitation.
> 
> Best Regards,
> 
> Antonio Gallardo.
> 
> On Vie, 14 de Enero de 2005, 15:14, Jakob Braeuchi dijo:
> 
>>hi antonio, carlos,
>>
>>a just added a path to support _simple_ expression for attributes.
>>ie. price + 10.
>>
>>the SqlHelper looks for operators + - / * only. we need a much better
>>parsing here that also can resolve multiple attributes in one
>>expressions. ie. price - bonus.
>>
>>jakob
>>
>>Antonio Gallardo schrieb:
>>
>>>Hi Carlos:
>>>
>>>Yes, you are right. It is still valid with the current 1.0.x (CVS
>>>versions). I wrote a test case for OJB (see attach). Seems like
>>>expresion
>>>are not evaluated at all.
>>>
>>>Best Regards,
>>>
>>>Antonio Gallardo
>>>
>>>On Mar, 11 de Enero de 2005, 18:30, Carlos Chávez dijo:
>>>
>>>
>>>>Hi.
>>>>
>>>>I have a question about ReportQueryByCriteria and the SQL created
>>>>
>>>>I have the following Query defined:
>>>>
>>>>ReportQueryByCriteria query = new ReportQueryByCriteria(Recibo.class,
>>>>criterio);
>>>>
>>>>query.setAttributes(new String[] {
>>>>    "rec_num", "rec_fecha", "cliente.cli_nombre",
>>>>    "COALESCE(rec_monto_cordobas,0)+COALESCE(rec_mora_cordobas,0)",
>>>>    "COALESCE(rec_monto_dolares,0)+COALESCE(rec_mora_dolares,0)" });
>>>>
>>>> query.addGroupBy(new String[] {
>>>>    "rec_num", "rec_fecha", "cliente.cli_nombre",
>>>>    "rec_monto_cordobas", "rec_monto_dolares",
>>>>    "rec_mora_cordobas",  "rec_mora_dolares" });
>>>>
>>>> query.addOrderBy("rec_num", true);
>>>>
>>>> Criteria criterioHaving = new Criteria();
>>>> criterioHaving.addEqualTo("max(reciboestadoList.rec_estado_id)",
>>>>                          new Integer(1));
>>>> query.setHavingCriteria(criterioHaving);
>>>>
>>>> The following is the SQL created:
>>>>
>>>> SELECT A0.REC_NUM, A0.REC_FECHA, A2.CLI_NOMBRE,
>>>>        COALESCE(rec_monto_cordobas,0)+COALESCE(rec_mora_cordobas,0),
>>>>        COALESCE(rec_monto_dolares,0)+COALESCE(rec_mora_dolares,0)
>>>> FROM (RECIBO A0 INNER JOIN RECIBOESTADO A1 ON A0.REC_NUM=A1.REC_NUM)
>>>>      INNER JOIN CLIENTE A2 ON A0.CLI_ID=A2.CLI_ID
>>>> GROUP BY A0.REC_NUM, A0.REC_FECHA, A2.CLI_NOMBRE,
>>>>A0.REC_MONTO_CORDOBAS,
>>>>          A0.REC_MONTO_DOLARES,A0.REC_MORA_CORDOBAS,A0.REC_MORA_DOLARES
>>>> HAVING max(A1.REC_ESTADO_ID) = ?
>>>> ORDER BY 1
>>>>
>>>> then my question is:
>>>>
>>>> why in this sentence:
>>>>   COALESCE(rec_monto_cordobas,0)+COALESCE(rec_mora_cordobas,0)
>>>> the field rec_monto_cordobas is not resolve like A0.rec_monto_cordobas
>>>> AND field rec_mora_cordobas is not resolve like A0.rec_mora_cordobas ?
>>>>
>>>> COALESCE is a function in PostgreSQL.
>>>>
>>>> any advice are welcome.
>>>>
>>>> I'm using db-ojb-1.0.1.
>>>>
>>>> Cheers.
>>>>
>>>>--
>>>>Carlos Chávez
>>>>
>>>>
>>>>------------------------------------------------------------------------
>>>>
>>>>Index: QueryTest.java
>>>>===================================================================
>>>>RCS file:
>>>>/home/cvspublic/db-ojb/src/test/org/apache/ojb/broker/QueryTest.java,v
>>>>retrieving revision 1.61.2.7
>>>>diff -u -r1.61.2.7 QueryTest.java
>>>>--- QueryTest.java	12 Dec 2004 01:35:12 -0000	1.61.2.7
>>>>+++ QueryTest.java	14 Jan 2005 10:40:37 -0000
>>>>@@ -11,6 +11,7 @@
>>>>import java.util.Vector;
>>>>
>>>>import org.apache.commons.lang.time.StopWatch;
>>>>+import org.apache.ojb.broker.accesslayer.sql.SqlGenerator;
>>>>import org.apache.ojb.broker.metadata.ClassDescriptor;
>>>>import org.apache.ojb.broker.platforms.PlatformHsqldbImpl;
>>>>import org.apache.ojb.broker.query.Criteria;
>>>>@@ -970,6 +971,20 @@
>>>>        int count = broker.getCount(q);
>>>>        assertEquals(results.size(), count);
>>>>
>>>>+    }
>>>>+
>>>>+    /**
>>>>+     * ReportQuery with Expression in column need to add table alias to
>>>>the field (price)
>>>>+     */
>>>>+    public void testReportQueryExpressionInStatement()
>>>>+    {
>>>>+        Criteria crit = new Criteria();
>>>>+        ReportQueryByCriteria q =
>>>>QueryFactory.newReportQuery(Article.class, crit);
>>>>+        q.setAttributes(new String[]{"articleId", "price + 0"});
>>>>+        ClassDescriptor cd =
>>>>broker.getClassDescriptor(q.getBaseClass());
>>>>+        SqlGenerator sqlg = broker.serviceSqlGenerator();
>>>>+        String sql = sqlg.getPreparedSelectStatement(q, cd);
>>>>+        assertTrue("Bad query generated. the 'price' field has not
>>>>table prefix. SQL Output: " + sql, sql.equalsIgnoreCase("SELECT
>>>>A0.Artikel_Nr,A0.PRICE + 0 FROM Artikel A0"));
>>>>    }
>>>>
>>>>    /**
>>>>
>>>>
>>>>------------------------------------------------------------------------
>>>>
>>>>---------------------------------------------------------------------
>>>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>>>For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message