Your OR clause is applied separately, so you're getting the Cartesian product of the two tables:
'or IDENTIFICATORI_VALORE.TIPO_RIGA_ID is null' Besides, it contradicts with 'where IDENTIFICATORI_VALORE.TIPO_RIGA_ID is not null' so you could leave both constraints out. Niels ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 6:37 PM To: [email protected] Subject: RE: discriminator and submap "Niels Beekman" <[EMAIL PROTECTED]> scritti il 12/02/2008 22:37:21 > Hi, > > I'm not familiar with the decode function, but I would check the > ResultSet debug logging and see if 'decode( IDENTIFICATORI_VALORE. > TIPO_RIGA_ID, null, 0, 1) as DI_TIPO_RIGA' actually returns the > results you're expecting. Thanks, Niels. I have done two things: I) I turned on the ResultSet logging log4j.logger.java.sql.ResultSet=TRACE, stdout II) To be sure to avoid type mismatch interferences (even if our DBA checked and comfirmed that the on-the-fly created DI_TIPO_RIGA was of type NUMBER (oracle) NUMERIC (jdbcType)) I changed both the query and the discriminator to a string type <discriminator column="DI_TIPO_RIGA" jdbcType="VARCHAR" javaType="java.lang.String"> <subMap value="myTrue" resultMap="mapEspressioneValoriTipoRigaMulti" /> select VALORE_ID ... , decode( IDENTIFICATORI_VALORE.TIPO_RIGA_ID, null, 'myFalse', 'myTrue') as DI_TIPO_RIGA There is no trace, in the logging for the ResultSet, of the DI_TIPO_RIGA_ID column! (your suspect was right) If you check the ( header , result ) pairs you get the following data VALORE_ID 3638 FORMULA_ID 470 FORMULA_ID 470 TIPO_RIGA_ID 0 TIPO_RIGA_ID null TESTO se( Conguaglio = 0; maggiore( DED_BASE.imp + DED_AGG.imp + DET_CON.imp + DET_FIG.imp [...] If you query directly the DB you can check that IDENTIFICATORI_VALORE.TIPO_RIGA_ID is null for VALORE_ID 3638 and here it has been reported with two different results, in spite of the IDENTIFICATORI_VALORE.TIPO_RIGA_ID = TIPI_DATO_RIGA.TIPO_RIGA_ID join clause There is somethin wrong. Trace (I have changed the layout of the query to make it readble) log4j:WARN No appenders could be found for logger (com.ibatis.common.jdbc.SimpleDataSource). log4j:WARN Please initialize the log4j system properly. DEBUG [main] - {pstm-100001} PreparedStatement: select count( VALORE_ID ) from IDENTIFICATORI_VALORE DEBUG [main] - {pstm-100001} Parameters: [] DEBUG [main] - {pstm-100001} Types: [] DEBUG [main] - {rset-100002} ResultSet DEBUG [main] - {rset-100002} Header: [COUNT(VALORE_ID)] DEBUG [main] - {rset-100002} Result: [6548] DEBUG [main] - {pstm-100004} PreparedStatement: select VALORE_ID , FORMULA_ID , IDENTIFICATORI_VALORE.TIPO_RIGA_ID TIPO_RIGA_ID , TESTO , decode( IDENTIFICATORI_VALORE.TIPO_RIGA_ID, null, 'myFalse', 'myTrue') as DI_TIPO_RIGA from IDENTIFICATORI_VALORE , TIPI_DATO_RIGA where IDENTIFICATORI_VALORE.TIPO_RIGA_ID is not null and IDENTIFICATORI_VALORE.TIPO_RIGA_ID = TIPI_DATO_RIGA.TIPO_RIGA_ID and FORMULA_ID not in ( select FORMULA_ID from IDENTIFICATORI_FORMULA where NORMATIVA_ID is null ) and TIPI_DATO_RIGA.TIPO_ELEMENTO_ID in ( select TIPO_DATO_ID from TIPI_DATO_ELEMENTO ) or IDENTIFICATORI_VALORE.TIPO_RIGA_ID is null DEBUG [main] - {pstm-100004} Parameters: [] DEBUG [main] - {pstm-100004} Types: [] DEBUG [main] - {rset-100005} ResultSet DEBUG [main] - {pstm-100006} PreparedStatement: select IDENTIFICATORE_ID from IDENTIFICATORI_FORMULA where FORMULA_ID = ? DEBUG [main] - {pstm-100006} Parameters: [470] DEBUG [main] - {pstm-100006} Types: [java.math.BigDecimal] DEBUG [main] - {rset-100007} ResultSet DEBUG [main] - {rset-100007} Header: [IDENTIFICATORE_ID] DEBUG [main] - {rset-100007} Result: [452] DEBUG [main] - {pstm-100008} PreparedStatement: select NORMATIVA_ID from IDENTIFICATORI_FORMULA where FORMULA_ID = ? DEBUG [main] - {pstm-100008} Parameters: [470] DEBUG [main] - {pstm-100008} Types: [java.math.BigDecimal] DEBUG [main] - {rset-100009} ResultSet DEBUG [main] - {rset-100009} Header: [NORMATIVA_ID] DEBUG [main] - {rset-100009} Result: [4] DEBUG [main] - {rset-100005} Header: [VALORE_ID, FORMULA_ID, FORMULA_ID, TIPO_RIGA_ID, TIPO_RIGA_ID, TESTO] DEBUG [main] - {rset-100005} Result: [3638, 470, 470, 0, null, se( Conguaglio = 0; maggiore( DED_BASE.imp + DED_AGG.imp + DET_CON.imp + DET_FIG.imp + DET_ALT.imp; ( IPT_ORD.ipn + IPT_SEP.ipn - IPT_ASS.ipn ) * -1 ); maggiore( DED_BASE.imp + DED_AGG.imp + DET_CON.imp + DET_FIG.imp + DET_ALT.imp; ( progressivo( IPT_ORD.ipn ) + progressivo( IPT_SEP.ipn ) - progressivo( IPT_ASS.ipn ) ) * -1 ) )] com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in it/finmatica/gpj/aa/frontedb/EspressioneFabbricaImpl.xml. --- The error occurred while applying a result map. --- Check the Gpj.mapEspressioneValoriTipoRigaMulti. --- The error happened while setting a property on the result object. --- Cause: com.ibatis.common.exception.NestedRuntimeException: Error setting properties of '[EMAIL PROTECTED]'. Cause: java.lang.IllegalArgumentException Caused by: java.lang.IllegalArgumentException Caused by: com.ibatis.common.exception.NestedRuntimeException: Error setting properties of '[EMAIL PROTECTED]'. Cause: java.lang.IllegalArgumentException Caused by: java.lang.IllegalArgumentException at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery WithCallback(GeneralStatement.java:188) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery ForList(GeneralStatement.java:123) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap ExecutorDelegate.java:610) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap ExecutorDelegate.java:584) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessi onImpl.java:101) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClient Impl.java:78) at it.finmatica.gpj.ec.istruzioni.EspressioneFabbricaImpl.getOggettiImpl(Es pressioneFabbricaImpl.java:185) at it.finmatica.gpj.aa.frontedb.InitFogliaFabbricaImpl.getOggettiImpl(InitF ogliaFabbricaImpl.java:119)
