You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Carlos Chávez <cc...@agssa.net> on 2005/01/12 01:30:22 UTC
ReportQueryByCriteria do not resolve name of field in the SQL
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
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi antonio,
i made a lot of refactorings in SqlQueryStatement (using non-pk-fields
for relationships etc.) so i don't know how difficult it would be to
just extract the new attribute stuff.
jakob
Antonio Gallardo schrieb:
> On Dom, 13 de Febrero de 2005, 11:02, Antonio Gallardo dijo:
>
>>On Dom, 13 de Febrero de 2005, 4:22, Jakob Braeuchi dijo:
>>
>>>hi antonio, carlos,
>>>
>>>i committed some enhancements to support expression in attributes ie:
>>>sum(0.9 * price * stock). be aware that all attributes contained in the
>>>expressions have to belong to the same table !
>>>
>>>please have a look at it.
>>
>>Great! Tomorrow we will do some tests about that!
>>
>>We will report back to the list.
>
>
> Sorry for the delay. But I noted today the fix is only in the current main
> trunk. Is posible to add the fix to the 1.0.x trunk?
>
> Best Regards,
>
> Antonio Gallardo
>
>
> ---------------------------------------------------------------------
> 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
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Antonio Gallardo <ag...@agssa.net>.
On Dom, 13 de Febrero de 2005, 11:02, Antonio Gallardo dijo:
> On Dom, 13 de Febrero de 2005, 4:22, Jakob Braeuchi dijo:
>> hi antonio, carlos,
>>
>> i committed some enhancements to support expression in attributes ie:
>> sum(0.9 * price * stock). be aware that all attributes contained in the
>> expressions have to belong to the same table !
>>
>> please have a look at it.
>
> Great! Tomorrow we will do some tests about that!
>
> We will report back to the list.
Sorry for the delay. But I noted today the fix is only in the current main
trunk. Is posible to add the fix to the 1.0.x trunk?
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Antonio Gallardo <ag...@agssa.net>.
On Dom, 13 de Febrero de 2005, 4:22, Jakob Braeuchi dijo:
> hi antonio, carlos,
>
> i committed some enhancements to support expression in attributes ie:
> sum(0.9 * price * stock). be aware that all attributes contained in the
> expressions have to belong to the same table !
>
> please have a look at it.
Great! Tomorrow we will do some tests about that!
We will report back to the list.
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi antonio, carlos,
i committed some enhancements to support expression in attributes ie:
sum(0.9 * price * stock). be aware that all attributes contained in the
expressions have to belong to the same table !
please have a look at it.
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: 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
>>
>
>
>
> ---------------------------------------------------------------------
> 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
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: 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
>>
>
>
>
> ---------------------------------------------------------------------
> 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
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Antonio Gallardo <ag...@agssa.net>.
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: 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Carlos Chávez <cc...@agssa.net>.
Jakob Braeuchi Escribio :-)
> hi antonio, carlos,
Hi Jakob.
>
> a just added a path to support _simple_ expression for attributes.
> ie. price + 10.
>
> the SqlHelper looks for operators + - / * only.
Thank for your help.
This is very helpfully.
> we need a much better
> parsing here that also can resolve multiple attributes in one
> expressions. ie. price - bonus.
Yes, we are discussing that need.
We need to create certain SQL with this kind of expression o maybe more
complex.
I don't know much about the code, i think we need modify the PathInfo
class, because that class only have the prefix, colName and suffix
field of the expression.
any hint are welcome.
So, thank for your help
Cheers.
>
> 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
>
--
Carlos Chávez
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: ReportQueryByCriteria do not resolve name of field in the SQL
Posted by Antonio Gallardo <ag...@agssa.net>.
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