ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mahajan, Atulya" <atulya.maha...@lehman.com>
Subject Trouble with making my dynamic clause work
Date Tue, 18 Dec 2007 15:49:32 GMT


> Please excuse the long message - just trying to provide the full
> context.
> Essentially I am having problems with a query using a dynamic clause.
> I create a dynamic clause that takes a list of Integers and creates a
> case statement out of it which has a 'not in ()' clause.
> This dynamic sql is then used in the query in the select and group by
> clause.
> 
> When I run the application, I get a 'not a GROUP BY expression'
> exception.
> However, if I run the query I see in the ibatis log and stick in the
> parameters it seems to run fine.
> 
> Can somebody point out potential reasons for such behavior?
> What am I doing wrong?
> 
> Thanks a lot.
> Atulya
> 
> This is the query xml
> 	<!--Query 1 -->
> 	<select id="selectFromPosSummary_rup"
> parameterClass="InstrumentPLSummaryRequest" resultMap="riskResultMap">
> 		<include refid="sql-selectClause-rup" />
> 			,POS_SUMMARY.GL_PL_STATUS_IND
> 		<include refid="sql-fromClause-rup" />
> 		<![CDATA[
> 				WHERE 
> 					POS_SUMMARY.strategy_num =
> STRATEGY_MAP.s_child 
> 					AND STRATEGY_MAP.s_child =
> ORG_STRATEGY.strategy_num 
> 		]]>
> 		<include refid="sql-groupByClause-rup" />
> 		,POS_SUMMARY.GL_PL_STATUS_IND
> 	</select>
> 
> This is a dynamic clause that takes a list of Integers and creates a
> case clause using that list (bla bla not in ())	
> 	<sql id="sql-dynamicExchangeList">
> 		<![CDATA[
> 			CASE 
> 		]]>
> 		<dynamic>
> 			<isNotEmpty prepend=" " property="exchangeList"
> >
> 				WHEN
> (POS_SUMMARY.counterpart_company_num NOT IN 
>                	<iterate conjunction="," property="exchangeList"
> open="(" close=" )) " >
>                		#exchangeList[]:INTEGER# 
>                 </iterate>
> 			</isNotEmpty> 
> 		</dynamic>
> 		<![CDATA[
> 			  	THEN 'OTC' 
> 			  	ELSE 'Exchange' 
> 				END
> 		]]>
> 	</sql>	
> 
> I use the said dynamic clause in the select and group by clauses as
> follows
> 	<sql id="sql-selectClause-rup">
> 		<![CDATA[
> 			SELECT 
> 		]]>
> 		<include refid="sql-dynamicExchangeList" />
> 		<![CDATA[
> 			instrument_type_cd,
> 			POS_SUMMARY.instrument_type_cd rpt_instr_desc,
> 			NVL(POS_SUMMARY.internal_ind, 0) internal_ind,
> 			SUM(POS_SUMMARY.risk_mtm_npv) risk_mtm,
> 			ORG_STRATEGY.strategy_name strategy_name
> 		]]>
> 	</sql>
> 
> 	<sql id="sql-fromClause-rup">
> 		<![CDATA[
> 			FROM 
> 			$tableName$ POS_SUMMARY,
> 
> 			ORG_STRATEGY, 
> 			$strategyMapTable$ STRATEGY_MAP 
> 		]]>
> 	</sql>
> 	<sql id="sql-groupByClause-rup">
> 		GROUP BY 
> 		ORG_STRATEGY.strategy_name, 
> 		<include refid="sql-dynamicExchangeList" />
> 		, 
> 		POS_SUMMARY.instrument_type_cd, 
> 		NVL(POS_SUMMARY.internal_ind, 0)
> 	</sql>
> 
> Now when I run this query from the application, I get oracle
> complaining about 'not a GROUP BY  expression'
> However when I plug in the query from the logs and stick in the
> parameters, it runs fine.
> 
> This makes it appear that the query as such is fine, but there is some
> problem with the way the parameters are being used.
> 
> From the logs:
> 
> 
> 20071218 10:11:18,645: DEBUG [] PreparedStatement {pstm-100267}
> PreparedStatement:           SELECT              CASE
> WHEN (POS_SUMMARY.counterpart_company_num NOT IN                  (
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                   ))                   THEN 'OTC'        ELSE
> 'Exchange'      END            instrument_type_cd,
> POS_SUMMARY.instrument_type_cd rpt_instr_desc,
> NVL(POS_SUMMARY.internal_ind, 0) internal_ind,
> SUM(POS_SUMMARY.risk_mtm_npv) risk_mtm,    ORG_STRATEGY.strategy_name
> strategy_name         ,POS_SUMMARY.GL_PL_STATUS_IND          FROM
> POS_SUMMARY_STRAT_20070928 POS_SUMMARY,         ORG_STRATEGY,
> Z_0_FLEXRPT_STRATEGY_MAP STRATEGY_MAP              WHERE
> POS_SUMMARY.strategy_num = STRATEGY_MAP.s_child       AND
> STRATEGY_MAP.s_child = ORG_STRATEGY.strategy_num          GROUP BY
> ORG_STRATEGY.strategy_name,           CASE                 WHEN
> (POS_SUMMARY.counterpart_company_num NOT IN                  (
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                  ,                  ?                  ,
> ?                   ))                   THEN 'OTC'        ELSE
> 'Exchange'      END        ,    POS_SUMMARY.instrument_type_cd,
> NVL(POS_SUMMARY.internal_ind, 0)     ,POS_SUMMARY.GL_PL_STATUS_IND
> 20071218 10:11:18,645: DEBUG [] PreparedStatement {pstm-100267}
> Parameters: [1396, 1398, 1402, 1404, 1411, 1963, 1965, 2347, 2453,
> 2689, 2715, 1006, 1018, 1396, 1398, 1402, 1404, 1411, 1963, 1965,
> 2347, 2453, 2689, 2715, 1006, 1018]
> 20071218 10:11:18,645: DEBUG [] PreparedStatement {pstm-100267} Types:
> [java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer, java.lang.Integer,
> java.lang.Integer, java.lang.Integer]
> 20071218 10:11:18,728: ERROR [] FlexReportDistributedProcesser Error
> in processing FlexReport request. Sending the following error
> response:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred while applying a parameter map.
> --- Check the
> INSTRUMENT_PL_SUMMARY.selectFromPosSummary_rup-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00979: not a GROUP BY expression
> 
> Caused by: java.sql.SQLException: ORA-00979: not a GROUP BY expression
> 	
> 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

This message is intended only for the personal and confidential use of the designated recipient(s)
named above.  If you are not the intended recipient of this message you are hereby notified
that any review, dissemination, distribution or copying of this message is strictly prohibited.
 This communication is for information purposes only and should not be regarded as an offer
to sell or as a solicitation of an offer to buy any financial product, an official confirmation
of any transaction, or as an official statement of Lehman Brothers.  Email transmission cannot
be guaranteed to be secure or error-free.  Therefore, we do not represent that this information
is complete or accurate and it should not be relied upon as such.  All information is subject
to change without notice.

--------
IRS Circular 230 Disclosure:
Please be advised that any discussion of U.S. tax matters contained within this communication
(including any attachments) is not intended or written to be used and cannot be used for the
purpose of (i) avoiding U.S. tax related penalties or (ii) promoting, marketing or recommending
to another party any transaction or matter addressed herein.



Mime
View raw message