cscdans
Well Known Member
Hi List,
when I wrote a BSSV with a select on a table, I had a stange behaviour and found out that brackets are set wrong by JDE when using an OR in a where clause. To simplyfy everything, I wrote an additional Test BSSV with the same behaviour. For this example it just shall do
SELECT F0005.KY, F0005.DL01, F0005.DL02
FROM F0005
WHERE F0005.SY=sysvar1 AND F0005.RT=rtvar1 OR F0005.SY=sysvar2 AND F0005.RT=rtvar2
ORDER BY F0005.SY ASC, F0005.RT ASC, F0005.KY ASC
I used the JDE expression builder and it generated:
BSSVDBWhereField[] whereFields =
{new BSSVDBWhereField(null, new BSSVDBField("F0005.SY"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_SY1()),
new BSSVDBWhereField(IDBService.AND, new BSSVDBField("F0005.RT"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_RT1()),
new BSSVDBWhereField(IDBService.OR, new BSSVDBField("F0005.SY"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_SY2()),
new BSSVDBWhereField(IDBService.AND, new BSSVDBField("F0005.RT"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_RT2())};
what is ok (looks very similar to c-selection struct, eh? ;-) )
but the log reports:
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE (((((DRSY = ? ) AND DRRT = ? ) OR DRSY = ? ) AND DRRT = ? )) ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] SQL statement parameter marker values are :
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param1 : 01_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param2 : 01_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param3 : H95_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param4 : AI_Types.VARCHAR,
So result fits to the logged sql:
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE (((((DRSY = '01' ) AND DRRT = '01' ) OR DRSY = 'H95' ) AND DRRT = 'AI' )) ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
when you resolve the brackets the result will be
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE DRSY = '01' AND DRRT = '01' AND DRRT = 'AI' OR DRSY = 'H95' AND DRRT = 'AI' ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
WHAT IS NOT EQUAL TO THE STATEMENT ENTERED IN THE JAVACODE WHICH EXSPECTED RESULT SHOULD BE:
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE DRSY = '01' AND DRRT = '01' OR DRSY = 'H95' AND DRRT = 'AI' ORDER BY DRSY ASC , DRRT ASC , DRKY ASC .
I zipped the BSSV, so you shall be able to test it yourself an you able to llok at the code.
I guess it is an error in the java library of oracle in oracle.e1.bssvfoundation.services
But perhaps someone has a tip or perhaps it is my fault and I do not see it.
Thank you and best regards,
Carsten
We use 8.12 TOOLS 8.98 Update 1, iSeriies with DB2/400. Webserver Oracle unbreakable linux 5 with Oracle Application Server 10.1.3.3.0
JDEveloper Version 10.1.3.4.0
when I wrote a BSSV with a select on a table, I had a stange behaviour and found out that brackets are set wrong by JDE when using an OR in a where clause. To simplyfy everything, I wrote an additional Test BSSV with the same behaviour. For this example it just shall do
SELECT F0005.KY, F0005.DL01, F0005.DL02
FROM F0005
WHERE F0005.SY=sysvar1 AND F0005.RT=rtvar1 OR F0005.SY=sysvar2 AND F0005.RT=rtvar2
ORDER BY F0005.SY ASC, F0005.RT ASC, F0005.KY ASC
I used the JDE expression builder and it generated:
BSSVDBWhereField[] whereFields =
{new BSSVDBWhereField(null, new BSSVDBField("F0005.SY"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_SY1()),
new BSSVDBWhereField(IDBService.AND, new BSSVDBField("F0005.RT"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_RT1()),
new BSSVDBWhereField(IDBService.OR, new BSSVDBField("F0005.SY"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_SY2()),
new BSSVDBWhereField(IDBService.AND, new BSSVDBField("F0005.RT"), IDBService.EQUALS, internalVO.getQueryWhereFields().getF0005_RT2())};
what is ok (looks very similar to c-selection struct, eh? ;-) )
but the log reports:
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE (((((DRSY = ? ) AND DRRT = ? ) OR DRSY = ? ) AND DRRT = ? )) ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] SQL statement parameter marker values are :
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param1 : 01_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param2 : 01_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param3 : H95_Types.VARCHAR,
14 Jul 2009 17:21:33,061 [APP ] - [JDBJ] Param4 : AI_Types.VARCHAR,
So result fits to the logged sql:
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE (((((DRSY = '01' ) AND DRRT = '01' ) OR DRSY = 'H95' ) AND DRRT = 'AI' )) ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
when you resolve the brackets the result will be
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE DRSY = '01' AND DRRT = '01' AND DRRT = 'AI' OR DRSY = 'H95' AND DRRT = 'AI' ORDER BY DRSY ASC , DRRT ASC , DRKY ASC
WHAT IS NOT EQUAL TO THE STATEMENT ENTERED IN THE JAVACODE WHICH EXSPECTED RESULT SHOULD BE:
SELECT DRKY,DRDL01,DRDL02,DRSY,DRRT FROM TESTCTL.F0005 WHERE DRSY = '01' AND DRRT = '01' OR DRSY = 'H95' AND DRRT = 'AI' ORDER BY DRSY ASC , DRRT ASC , DRKY ASC .
I zipped the BSSV, so you shall be able to test it yourself an you able to llok at the code.
I guess it is an error in the java library of oracle in oracle.e1.bssvfoundation.services
But perhaps someone has a tip or perhaps it is my fault and I do not see it.
Thank you and best regards,
Carsten
We use 8.12 TOOLS 8.98 Update 1, iSeriies with DB2/400. Webserver Oracle unbreakable linux 5 with Oracle Application Server 10.1.3.3.0
JDEveloper Version 10.1.3.4.0
