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]
