Hi,
A happy new year to you too. Your testcase works on the latest
OpenJPA/1.2.1, but with one problem that I had to work around. The
problem is with the unique=true specification on these columns in
GeneralLedgerEntry.java:
@Column(name="G1008COD",unique = true)
@XmlElement
private String code;
and
@Column(name="G1008POS",unique = true)
@XmlElement
private String position;
The corresponding CREATE TABLE statement looks like this:
CREATE TABLE GL1GLET (
...<snip>...
PRIMARY KEY (G1008ID),
CONSTRAINT UNQ_ UNIQUE (G1008COD) DEFERRABLE,
CONSTRAINT UNQ_ UNIQUE (G1008POS) DEFERRABLE
)
This create table fails because the names of the constraints are the
same: "UNQ_".
Once I took out the unique=true clause from the @Column annotation,
the testcase worked fine.
It looks like this same-constraint-name bug has been fixed in
OpenJPA-2.0 where constraint names are composed of table+column names
to make them unique. But I could not find a JIRA issue that addressed
this problem. If you wish, please file one against 1.2.x - it's
probably a matter of backporting the fix.
FYI, I'm pasting the native query (with parameter markers retained)
generated by 1.2.1 for your test case below.
Thanks,
Dinkar
SELECT
t0.G1017ID, t0.G1017APPLYDT, t0.G1017APPROVDT,
t12.O1001ID, t12.O1001COD, t12.O1001NAM, t0.G1017CASHCOLVCR,
t0.G1017EXPLAN, t0.G1017FOLIONO, t0.G1017INTBRN, t0.G1017INTCOMP,
t0.G1017ISSUEDT, t0.G1017ORIGVCHNO, t0.G1017SRLNO, t0.G1017TRANSNO,
t0.G1017VCRNO, t0.G1017VCRSUSPNO FROM GL1VOUCHER t0,
GL1VOUCHERENT t1, GL1ACCT t2, GL1DCTRVRET t3, OF1BRANCH t12
WHERE (
? = ? AND
(1 = 1 OR t0.G1017BRAISSUE IS NULL) AND
(1 = 1 OR t0.G1017BRAISSUE IN (NULL)) AND
(1 = 1 OR t0.G1017ISSUEDT >= NULL) AND
(1 = 1 OR t0.G1017ISSUEDT <= NULL) AND
(1 = 1 OR t0.G1017APPLYDT >= NULL) AND
(1 = 1 OR t0.G1017APPLYDT <= NULL) AND
(1 = 1 OR t0.G1017APPROVDT >= NULL) AND
(1 = 1 OR t0.G1017APPROVDT <= NULL) AND
(1 = 1 OR t0.G1017VCRNO >= NULL) AND
(1 = 1 OR t0.G1017VCRNO <= NULL) AND
(1 = 1 OR t0.G1017TRANSNO IS NULL) AND
(1 = 1 OR t2.G1GLET2ACCT IN (NULL)) AND
(1 = 1 OR t0.G1017CASHCOLVCR IS NULL) AND
(1 = 1 OR t0.G1017INTBRN IS NULL) AND
(1 = 1 OR t2.G1BRNCH2ACCT IN (NULL)) AND
(1 = 1 OR t0.G1017BRAISSUE IN (NULL)) AND
(1 = 1 OR t0.G1017INTCOMP IS NULL) AND
(1 = 1 OR t2.G1CURR2ACCT IS NULL) AND
(1 = 1 OR t2.G1CURR2ACCT IN (NULL)) AND
(1 = 1 OR t1.G1018CURT2VCREN IS NULL) AND
(1 = 1 OR t1.G1018CURT2VCREN IN (NULL)) AND
(1 = 1 OR t1.G1018CURRRT IN (NULL)) AND
(1 = 1 OR t0.G1017VCRNO IN (NULL)) AND
(1 = 1 OR t0.G1017FOLIONO IN (NULL)) AND
(1 = 1 OR t0.G1017EXPLAN IS NULL) AND
(1 = 1 OR t1.G1018EXPLAN IS NULL) AND
(1 = 1 OR NULL >= ANY (
SELECT t4.G1018AMNT
FROM GL1VOUCHERENT t4, GL1ACCT t5
WHERE (
t4.G1VCR2VRCENT = t0.G1017ID AND
(1 = 1 OR t5.G1CURR2ACCT IS NULL) AND
(1 = 1 OR 1 <> 1 AND t4.G1018AMNT < ? OR 1 <> 1 AND
t4.G1018AMNT > ?)
) AND
t4.G1ACC2VCRENT = t5.G1004ID(+)
)
) AND
(1 = 1 OR NULL <= ANY (
SELECT t6.G1018AMNT
FROM GL1VOUCHERENT t6, GL1ACCT t7
WHERE (
t6.G1VCR2VRCENT = t0.G1017ID AND
(1 = 1 OR t7.G1CURR2ACCT IS NULL) AND
(1 = 1 OR 1 <> 1 AND t6.G1018AMNT < ? OR 1 <> 1 AND
t6.G1018AMNT > ?)
) AND
t6.G1ACC2VCRENT = t7.G1004ID(+)
)
) AND
(1 = 1 OR NULL <= (
SELECT SUM(ABS(t8.G1018AMNT)) FROM GL1VOUCHERENT t8, GL1ACCT t9
WHERE (
t8.G1VCR2VRCENT = t0.G1017ID AND
(1 = 1 OR t9.G1CURR2ACCT IS NULL)
AND (1 = 1 OR 1 <> 1 AND t8.G1018AMNT < ? OR 1 <> 1 AND
t8.G1018AMNT > ?)
) AND
t8.G1ACC2VCRENT = t9.G1004ID(+)
)
) AND
(1 = 1 OR NULL >= (
SELECT SUM(ABS(t10.G1018AMNT))
FROM GL1VOUCHERENT t10, GL1ACCT t11
WHERE (
t10.G1VCR2VRCENT = t0.G1017ID
AND (1 = 1 OR t11.G1CURR2ACCT IS NULL)
AND (1 = 1 OR 1 <> 1 AND t10.G1018AMNT < ? OR 1 <> 1 AND
t10.G1018AMNT > ?)
) AND
t10.G1ACC2VCRENT = t11.G1004ID(+)
)
)
AND (1 = 1 OR t2.G1004SRLNO >= NULL)
AND (1 = 1 OR t2.G1004SRLNO <= NULL)
AND (1 = 1 OR t2.G1004COD IS NULL)
AND (1 = 1 OR t3.G1CC2DCTRVRET IS NULL)
AND (1 = 1 OR t0.G1017FOLIONO IS NULL)
AND (1 = 1 OR t1.G1018FOLIONO IS NULL)
AND (1 = 1 OR t1.G1018BOP IS NULL)
AND 1 = 1
) AND
t0.G1017ID = t1.G1VCR2VRCENT(+) AND
t0.G1017BRAISSUE = t12.O1001ID(+) AND
t1.G1ACC2VCRENT = t2.G1004ID(+)
AND t1.G1018ID = t3.G1VRET2DCTRVRET(+)
