hi antonio,
this issue is not new at all. see http://issues.apache.org/scarab/issues/id/OJB242
the problem i see so far is: ojb assumes there is only one attribute per selection criteria. simple resolving of each token in the criteria or attribute could be too slow.
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: [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]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
