[
https://issues.apache.org/jira/browse/OPENJPA-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12977604#action_12977604
]
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.
Regards,
Jai (JaiKumar Kochi Rammohan)
Global Ledger User Interface (GLUI) Team, IGA US Finance,
IBM India Pvt Ltd,
DC7-3-049,
Blk 7, DLF IT Park, 3rd Flr,
1/124, Sivaji Gardens, Moonlight Stop,
Nandambakkam Post, Ramapuram,
Chennai - 600 089, Tamilnadu, India.
Work : +91-44-22723586
Board : +91-44-22720000 X 3586
TieLine/VOIP: +92 67568,
Mobile: +91-9840820310
email:jaikumar...@in.ibm.com
"Fay Wang (JIRA)" <j...@apache.org>
04/01/2011 23:56
To
Jaikumar K Rammohan/India/i...@ibmin
cc
Subject
[jira] Commented: (OPENJPA-1914) Issue with OpenJPA 1.2.1 while using
subquery.
[
https://issues.apache.org/jira/browse/OPENJPA-1914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12977392#action_12977392
]
Fay Wang commented on OPENJPA-1914:
-----------------------------------
According to the spec, the BNF of the in_expression, subquery are:
in_expression ::=
state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
subquery ::= simple_select_clause subquery_from_clause
[where_clause][groupby_clause] [having_clause]
simple_select_clause ::= SELECT [DISTINCT] simple_select_expression
As can be seen from the BNF, the LHS of the in_expression can be one and
only one path expression and the subquery can select one and only one
expression or variable.
The where clause in your JPQL, therefore, is incorrect:
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)";
system. Working from standalone java program. using log4j-1.2.14.jar and
geronimo-jpa_3.0_spec-1.0.jar
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)";
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.
> 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.
