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]
