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: [EMAIL PROTECTED]
>>>For additional commands, e-mail: [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> 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]
