You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "Mahajan, Atulya" <at...@lehman.com> on 2007/12/18 16:49:32 UTC
Trouble with making my dynamic clause work
> 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.