You mean like this?
List criteria = new LinkedList();
criteria.add(1.0); criteria.add(EntityOperator.AND);
criteria.add(10.0);
new EntityExpr("amount", EntityOperator.BETWEEN,
criteria);
I cannot do this because EntityExpr (see about line
64) insists on having 0 or 2 criteria, not 3.
If you have actually got this working, would you mind
sending me a code snippet as an example? Agradecia
muito.
cheers,
cameron
> Use BETWEEN and more new EntityCondition AND to
> values.
> Query ie: fieldName BETWEEN 10 AND 20.
>
>
>
>
>
> 2007/6/30, Cameron Smith
> <[EMAIL PROTECTED]>:
> > Hi, in the last few weeks I have come across a
> couple
> > of issues with the SQL Processor generating
> incorrect
> > SQL. I am using MySQL 5.0, but I stress that
> these
> > issues would also cause problems in the two other
> > RDBMS I am familiar with (Oracle and PostgreSQL).
> >
> > I have dug around this part of the Entity Engine
> code
> > and I believe I could write a fix, but first I
> wanted
> > to check that I am not missing something or using
> the
> > EE in an incorrect way.
> >
> > === Issue 1 - BETWEEN operator ===
> > EntityOperator.BETWEEN generates incorrect SQL.
> For
> > instance, if I use something like (Java 1.5):
> >
> > List criteria = new LinkedList();
> > criteria.add(1.0); criteria.add(10.0);
> > new EntityExpr("amount", EntityOperator.BETWEEN,
> > criteria);
> >
> > The EntityExpr, if used in a delegator query,
> will
> > generate the following SQL:
> > ... BETWEEN (1.0, 10.0)
> >
> > However this syntax is incorrect in the following
> > RDBMS:
> > MySQL 5.0:
> >
>
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
> > PostgreSQL 8.2:
> >
>
http://www.postgresql.org/docs/8.2/static/functions-comparison.html
> > Oracle 9i: (couldn't be bothered to fight my way
> > through Oracle's online docs but I did check my
> ref.
> > book here and my own recollection of 8 years
> > programming with Oracle).
> >
> > Has anyone used EntityOperator.BETWEEN
> successfully?
> >
> > === Issue 2 - Column aliasing ===
> > I only noticed this issue when I synced our local,
> > customized OFBiz (based on r502669) with the 4.0
> > branch (r545673). But I believe that the issue
> always
> > existed, and it is just that the alterations to
> some
> > of the accounting View Entities in r545673,
> brought it
> > to light.
> >
> > When the EE SQL Processor does a query which View
> > Entities, it aliases each component table of the
> > Entity to a short name within the query, and
> prefixes
> > all column references in the SELECT clause with
> that
> > short name. This is correct. However it does NOT
> > prefix columns in the WHERE clause with that short
> > name. Or at least, I cannot see how to make it do
> > this. Because of this, MySQL at least rejects
> the
> > query because some of the column names in the
> WHERE
> > clause are ambiguous.
> >
> > Here is a real example of a query which worked
> > perfectly in r502669, but breaks in r545673,
> because
> > of the extra columns added into the
> > AcctgTransAndEntries View Entity. Once again, I
> > stress that r545673 is not the culprit - the SQL
> > Processor appears to have always had this
> limitation,
> > it just does not show itself regularly!
> >
> > Here is the code to build up the query:
> > public List<GenericValue>
> > findPostedTransactionsLike(String orgId, String
> txId)
> > throws GenericEntityException
> > {
> > List criteria = exprEqualsAll("isPosted",
> "Y");
> > criteria.add(new EntityExpr("acctgTransId",
> > EntityOperator.LIKE, txId + "%"));
> > List orderBy =
> UtilMisc.toList("acctgTransId",
> > "acctgTransEntrySeqId");
> > return
> > _delegator.findByAnd("AcctgTransAndEntries",
> criteria,
> > orderBy);
> > }
> >
> > And here is the generated SQL and MySQL's error
> > message. The generated SQL would work perfectly
> if
> > the columns in the WHERE clause were prefixed with
> the
> > respective alias names:
> >
> > SELECT ATR.IS_POSTED AS IS_POSTED,
> > ATR.GL_FISCAL_TYPE_ID AS GL_FISCAL_TYPE_ID,
> > ATR.ACCTG_TRANS_TYPE_ID AS ACCTG_TRANS_TYPE_ID,
> > ATR.TRANSACTION_DATE AS TRANSACTION_DATE,
> > ATR.DESCRIPTION AS TRANS_DESCRIPTION,
> ATR.INVOICE_ID
> > AS INVOICE_ID, ATR.PAYMENT_ID AS PAYMENT_ID,
> > ATR.SHIPMENT_ID AS SHIPMENT_ID, ATR.RECEIPT_ID AS
> > RECEIPT_ID, ATR.INVENTORY_ITEM_ID AS
> > INVENTORY_ITEM_ID, ATR.WORK_EFFORT_ID AS
> > WORK_EFFORT_ID, ATR.PHYSICAL_INVENTORY_ID AS
> > PHYSICAL_INVENTORY_ID, ATR.VOUCHER_REF AS
> VOUCHER_REF,
> > ATR.GL_JOURNAL_ID AS GL_JOURNAL_ID,
> ATE.ACCTG_TRANS_ID
> > AS ACCTG_TRANS_ID, ATE.ACCTG_TRANS_ENTRY_SEQ_ID AS
> > ACCTG_TRANS_ENTRY_SEQ_ID, ATE.GL_ACCOUNT_ID AS
> > GL_ACCOUNT_ID, ATE.PRODUCT_ID AS PRODUCT_ID,
> > ATE.DEBIT_CREDIT_FLAG AS DEBIT_CREDIT_FLAG,
> ATE.AMOUNT
> > AS AMOUNT, ATE.CURRENCY_UOM_ID AS CURRENCY_UOM_ID,
> > ATE.ORGANIZATION_PARTY_ID AS
> ORGANIZATION_PARTY_ID,
> > GLA.GL_ACCOUNT_TYPE_ID AS GL_ACCOUNT_TYPE_ID,
> > GLAC.GL_ACCOUNT_CLASS_ID AS GL_ACCOUNT_CLASS_ID,
> > ATE.PARTY_ID AS PARTY_ID, ATE.RECONCILE_STATUS_ID
> AS
> > RECONCILE_STATUS_ID, ATE.ACCTG_TRANS_ENTRY_TYPE_ID
> AS
> > ACCTG_TRANS_ENTRY_TYPE_ID, ATE.DESCRIPTION AS
> > DESCRIPTION FROM ACCTG_TRANS ATR INNER JOIN
> > ACCTG_TRANS_ENTRY ATE ON ATR.ACCTG_TRANS_ID =
> > ATE.ACCTG_TRANS_ID INNER JOIN GL_ACCOUNT GLA ON
> > ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID INNER JOIN
> > GL_ACCOUNT_CLASS GLAC ON GLA.GL_ACCOUNT_CLASS_ID =
> > GLAC.GL_ACCOUNT_CLASS_ID WHERE (IS_POSTED = ? AND
> > ACCTG_TRANS_ID LIKE ?) ORDER BY ACCTG_TRANS_ID
> ASC,
> > ACCTG_TRANS_ENTRY_SEQ_ID ASC (Column
> 'ACCTG_TRANS_ID'
> > in where clause is ambiguous)
> >
> > ========== end of message ==============
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
___________________________________________________________
> > Yahoo! Answers - Got a question? Someone out there
> knows the answer. Try it
> > now.
> > http://uk.answers.yahoo.com/
> >
>
>
___________________________________________________________
New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at
the Yahoo! Mail Championships. Plus: play games and win prizes.
http://uk.rd.yahoo.com/evt=44106/*http://mail.yahoo.net/uk
