[
https://issues.apache.org/jira/browse/OPENJPA-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12977605#action_12977605
]
JaiKumar commented on OPENJPA-1914:
-----------------------------------
Thanks for the response,
I understand from your statement that only one column can be used in Left hand
side of NOT IN operator. And two columns cannot be supported as in original
query.
i.e, WHERE (jrNbr.countryCode) NOT IN (SELECT vNbr.countryCode FROM
VoucherNumber vNbr,Country ctyEntity WHERE vNbr.countryCode = ctyEntity.cty AND
ctyEntity.cln_identity=:clnIdentity)";
However the whole idea of using two columns on LHS of NOT IN operator came to
me since its supported in DB2 Database with Native sql, for which OpenJPA ver
1.2.1 was failing ..
Even its supported by most of other DB like oracle, sqlserver etc. and as well.
Using a single table column will not provide similar results when you want to
check two column data against single record.
Native SQL which will work in client tools for most of database are provided
below.
UPDATE VITSCM.VITTBGE jnbr SET jnbr.updated_on=:updatedOn WHERE
(jnbr.cty,jnbr.src) NOT IN(SELECT a.cty,a.src FROM VITSCF.CFLTBGE
a,VITSCM.VITTCTY b WHERE a.cty = b.cty AND b.cln_identity=:clnIdentity)
Please provide an alternative for this.
> Issue with OpenJPA 1.2.1 while using subquery.
> ----------------------------------------------
>
> Key: OPENJPA-1914
> URL: https://issues.apache.org/jira/browse/OPENJPA-1914
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.2.1
> Environment: Database server = DB2 z/OS 9.1.5 in AIX system.
> Working from standalone java program. using log4j-1.2.14.jar and
> geronimo-jpa_3.0_spec-1.0.jar
> Reporter: JaiKumar
>
> On using below code to update an JournalNumber entity ..
> tx = entityManager.getTransaction();
> tx.begin();
> System.out.println("...Clone :" + cloneId);
> String query = "UPDATE JournalNumber jrNbr SET
> jrNbr.updated_on=:updatedOn WHERE (jrNbr.countryCode,jrNbr.sourceCode) NOT
> IN(SELECT vNbr.countryCode,vNbr.sourceCode FROM VoucherNumber vNbr,Country
> ctyEntity WHERE vNbr.countryCode = ctyEntity.cty AND
> ctyEntity.cln_identity=:clnIdentity)";
> Query q = entityManager.createQuery(query);
> q.setParameter("updatedOn", new Date());
> q.setParameter("clnIdentity", cloneId);
> q.executeUpdate();
> tx.commit();
> I get below error ..
> <openjpa-1.2.1-r752877:753278 nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Encountered "jrNbr .
> countryCode ," at character 67, but expected: ["(", ")", "*", "+", "-", ".",
> "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY",
> "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT",
> "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
> "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING",
> "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE",
> "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF",
> "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING",
> "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE", <DECIMAL_LITERAL>,
> <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
