db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Carlos Chávez <ccha...@agssa.net>
Subject Re: ReportQueryByCriteria do not resolve name of field in the SQL
Date Fri, 14 Jan 2005 21:58:36 GMT

Jakob Braeuchi Escribio :-)
> hi antonio, carlos,

  Hi Jakob.

>
> a just added a path to support _simple_ expression for attributes.
> ie. price + 10.
>
> the SqlHelper looks for operators + - / * only.

  Thank for your help.
  This is very helpfully.

> we need a much better
> parsing here that also can resolve multiple attributes in one
> expressions. ie. price - bonus.

 Yes, we are discussing that need.
 We need to create certain SQL with this kind of expression o maybe more
 complex.

 I don't know much about the code, i think we need modify the PathInfo
 class, because that class only have the prefix, colName and suffix
 field of the expression.

 any hint are welcome.

 So, thank for your help

 Cheers.

>
> 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
>


-- 
Carlos Chávez

---------------------------------------------------------------------
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