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 Fri, 14 Jan 2005 21:14:49 GMT
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


Mime
View raw message