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 kiran vuppla <ki...@yahoo.com> on 2008/08/19 15:35:38 UTC
Re: Invalid Column Name error
Can some one please let me know what would be the issue?
Thanks,
Kiran
--- On Mon, 8/18/08, kiran vuppla <ki...@yahoo.com> wrote:
From: kiran vuppla <ki...@yahoo.com>
Subject: Invalid Column Name error
To: "iBatis Forum" <us...@ibatis.apache.org>
Date: Monday, August 18, 2008, 7:43 PM
I am getting the following error, I will appreciate if some can let me know what is the issue. Thanks in Advance.
�
--- The error occurred while applying a result map.�
--- Check the merchant.getMaxValue.�
--- Check the result mapping for the 'max' property.
--- Cause: java.sql.SQLException: Invalid column name
�at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
�at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
�at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
�at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
�at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
�at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)
�at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
�... 72 more
�
�
<resultMap id="DistributionResult" class="Distribution">
�<result property="totalProducts" column="TOTAL_PRODUCTS"/>
�<result property="totalAmt" column="TOTAL_AMT"/>
�<result property="merchant.id"� column="TERM_ID"/>
�<result property="max" column="{merchantId = TERM_ID,#merchants#,#startDate#,#endDate#}" select="merchant.getMaxValue"/>
</resultMap>
�
<select id="getDistribution" parameterClass="map" resultMap="DistributionResult">
SELECT COUNT(*) as TOTAL_PRODUCTS
��� , SUM(a.amount) as TOTAL_AMT
��� , A.TERMID as TERM_ID
������ FROM tbl a, tbl b
������ WHERE a.termid = b.id�
������ <iterate property="merchants" open=" and a.SATHCDHACCTKEY in (" close = ")" conjunction=",">
������ #merchants[]#
����� </iterate>
AND a.dtathdate BETWEEN #startDate# AND #endDate# group by A.TERMID
</select>
�
<select id="getMaxVALue" resultClass="int">
SELECT MAX(subTbl.cnt1) as value from (SELECT COUNT(A.KEY ) as cnt1
FROM ath a, merchantterminal m WHERE A.SATHTERMID = M.LID and M.LID = #merchantId#
�<iterate property="merchants" open=" and a.KEY in (" close = ")" conjunction=",">
������ #merchants[]#
����� </iterate>
AND a.dtathdate BETWEEN #startDate# AND #endDate# group by to_char(a.dtathdate,'YYYYMMDD') ) subTbl
</select>
�
Thanks
Kiran
Re: Invalid Column Name error
Posted by Clinton Begin <cl...@gmail.com>.
No, you can't pass the list in.. The {prop=col} syntax is simply for
composite foreign keys. The results are literal column values and are not
materialized into an object of any type. To do what you want, just use two
separate calls.
Clinton
On Tue, Aug 19, 2008 at 8:37 AM, kiran vuppla
<ki...@yahoo.com>wrote:
> Thanks for the reply.
>
> I tried using:
>
>
> column="{merchantId=TERM_ID,merchants=#merchants#,startDate=#startDate#,endDate=#endDate#}"
>
> But still it is not working.
>
> I am retrieving the #merchants# list from the main select statement and I
> want to pass the same parameter to the sub select. is it not possible?
>
> It seems like the returned column from the sub select is not matching to
> property in the resultMap but everything seems ok for me. It would be more
> helpful if some one can let me know if syntax is right or if not i would
> appreciate if they can provide the syntax.
>
> Thanks,
> Kiran
>
>
> --- On *Tue, 8/19/08, Clinton Begin <cl...@gmail.com>* wrote:
>
> From: Clinton Begin <cl...@gmail.com>
> Subject: Re: Invalid Column Name error
> To: user-java@ibatis.apache.org, kirankumar_vuppla@yahoo.com
> Date: Tuesday, August 19, 2008, 9:23 AM
>
>
> {merchantId = TERM_ID,#merchants#,#startDate#,#endDate#}
>
> That's formatted incorrectly... it should be:
>
> {param1=col1, param2=col2, param3=col3}
>
> And the second problem is that the #merchants# column will not be loaded as
> a collection so you can't use the merchants[] iterator like that.
>
> Clinton
>
> On Tue, Aug 19, 2008 at 7:35 AM, kiran vuppla <kirankumar_vuppla@yahoo.com
> > wrote:
>
>> Can some one please let me know what would be the issue?
>>
>> Thanks,
>> Kiran
>>
>> --- On *Mon, 8/18/08, kiran vuppla <ki...@yahoo.com>* wrote:
>>
>> From: kiran vuppla <ki...@yahoo.com>
>> Subject: Invalid Column Name error
>> To: "iBatis Forum" <us...@ibatis.apache.org>
>> Date: Monday, August 18, 2008, 7:43 PM
>>
>>
>> I am getting the following error, I will appreciate if some can let me
>> know what is the issue. Thanks in Advance.
>>
>> --- The error occurred while applying a result map.
>> --- Check the merchant.getMaxValue.
>> --- Check the result mapping for the 'max' property.
>> --- Cause: java.sql.SQLException: Invalid column name
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
>> at
>> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)
>> at
>> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
>> ... 72 more
>>
>>
>> <resultMap id="DistributionResult" class="Distribution">
>> <result property="totalProducts" column="TOTAL_PRODUCTS"/>
>> <result property="totalAmt" column="TOTAL_AMT"/>
>> <result property="merchant.id" column="TERM_ID"/>
>> <result property="max" column="{merchantId =
>> TERM_ID,#merchants#,#startDate#,#endDate#}" select="merchant.getMaxValue"/>
>> </resultMap>
>>
>> <select id="getDistribution" parameterClass="map"
>> resultMap="DistributionResult">
>> SELECT COUNT(*) as TOTAL_PRODUCTS
>> , SUM(a.amount) as TOTAL_AMT
>> , A.TERMID as TERM_ID
>> FROM tbl a, tbl b
>> WHERE a.termid = b.id
>> <iterate property="merchants" open=" and a.SATHCDHACCTKEY in ("
>> close = ")" conjunction=",">
>> #merchants[]#
>> </iterate>
>> AND a.dtathdate BETWEEN #startDate# AND #endDate# group by A.TERMID
>> </select>
>>
>> <select id="getMaxVALue" resultClass="int">
>> SELECT MAX(subTbl.cnt1) as value from (SELECT COUNT(A.KEY ) as cnt1
>> FROM ath a, merchantterminal m WHERE A.SATHTERMID = M.LID and M.LID =
>> #merchantId#
>> <iterate property="merchants" open=" and a.KEY in (" close = ")"
>> conjunction=",">
>> #merchants[]#
>> </iterate>
>> AND a.dtathdate BETWEEN #startDate# AND #endDate# group by
>> to_char(a.dtathdate,'YYYYMMDD') ) subTbl
>> </select>
>>
>> Thanks
>> Kiran
>>
>>
>>
>
>
Re: Invalid Column Name error
Posted by kiran vuppla <ki...@yahoo.com>.
Thanks for the reply.
�
I tried using:
�
column="{merchantId=TERM_ID,merchants=#merchants#,startDate=#startDate#,endDate=#endDate#}"
�
But still it is not working.
�
I am retrieving the #merchants# list from the main select statement and I want to pass the same parameter to the sub select. is it not possible?
�
It seems like the returned column from the sub select is not matching to property in the resultMap but everything seems ok for me. It would be more helpful if some one can let me know if syntax is right or if not i would appreciate if they can provide the syntax.
�
Thanks,
Kiran
--- On Tue, 8/19/08, Clinton Begin <cl...@gmail.com> wrote:
From: Clinton Begin <cl...@gmail.com>
Subject: Re: Invalid Column Name error
To: user-java@ibatis.apache.org, kirankumar_vuppla@yahoo.com
Date: Tuesday, August 19, 2008, 9:23 AM
{merchantId = TERM_ID,#merchants#,#startDate#,#endDate#}
That's formatted incorrectly... it should be:
{param1=col1, param2=col2, param3=col3}
And the second problem is that the #merchants# column will not be loaded as a collection so you can't use the merchants[] iterator like that.�
Clinton
On Tue, Aug 19, 2008 at 7:35 AM, kiran vuppla <ki...@yahoo.com> wrote:
Can some one please let me know what would be the issue?
Thanks,
Kiran
--- On Mon, 8/18/08, kiran vuppla <ki...@yahoo.com> wrote:
From: kiran vuppla <ki...@yahoo.com>
Subject: Invalid Column Name error
To: "iBatis Forum" <us...@ibatis.apache.org>
Date: Monday, August 18, 2008, 7:43 PM
I am getting the following error, I will appreciate if some can let me know what is the issue. Thanks in Advance.
�
--- The error occurred while applying a result map.�
--- Check the merchant.getMaxValue.�
--- Check the result mapping for the 'max' property.
--- Cause: java.sql.SQLException: Invalid column name
�at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
�at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
�at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
�at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
�at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
�at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)
�at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
�... 72 more
�
�
<resultMap id="DistributionResult" class="Distribution">
�<result property="totalProducts" column="TOTAL_PRODUCTS"/>
�<result property="totalAmt" column="TOTAL_AMT"/>
�<result property="merchant.id"� column="TERM_ID"/>
�<result property="max" column="{merchantId = TERM_ID,#merchants#,#startDate#,#endDate#}" select="merchant.getMaxValue"/>
</resultMap>
�
<select id="getDistribution" parameterClass="map" resultMap="DistributionResult">
SELECT COUNT(*) as TOTAL_PRODUCTS
��� , SUM(a.amount) as TOTAL_AMT
��� , A.TERMID as TERM_ID
������ FROM tbl a, tbl b
������ WHERE a.termid = b.id�
������ <iterate property="merchants" open=" and a.SATHCDHACCTKEY in (" close = ")" conjunction=",">
������ #merchants[]#
����� </iterate>
AND a.dtathdate BETWEEN #startDate# AND #endDate# group by A.TERMID
</select>
�
<select id="getMaxVALue" resultClass="int">
SELECT MAX(subTbl.cnt1) as value from (SELECT COUNT(A.KEY ) as cnt1
FROM ath a, merchantterminal m WHERE A.SATHTERMID = M.LID and M.LID = #merchantId#
�<iterate property="merchants" open=" and a.KEY in (" close = ")" conjunction=",">
������ #merchants[]#
����� </iterate>
AND a.dtathdate BETWEEN #startDate# AND #endDate# group by to_char(a.dtathdate,'YYYYMMDD') ) subTbl
</select>
�
Thanks
Kiran
Re: Invalid Column Name error
Posted by Clinton Begin <cl...@gmail.com>.
{merchantId = TERM_ID,#merchants#,#startDate#,#endDate#}
That's formatted incorrectly... it should be:
{param1=col1, param2=col2, param3=col3}
And the second problem is that the #merchants# column will not be loaded as
a collection so you can't use the merchants[] iterator like that.
Clinton
On Tue, Aug 19, 2008 at 7:35 AM, kiran vuppla
<ki...@yahoo.com>wrote:
> Can some one please let me know what would be the issue?
>
> Thanks,
> Kiran
>
> --- On *Mon, 8/18/08, kiran vuppla <ki...@yahoo.com>* wrote:
>
> From: kiran vuppla <ki...@yahoo.com>
> Subject: Invalid Column Name error
> To: "iBatis Forum" <us...@ibatis.apache.org>
> Date: Monday, August 18, 2008, 7:43 PM
>
>
> I am getting the following error, I will appreciate if some can let me
> know what is the issue. Thanks in Advance.
>
> --- The error occurred while applying a result map.
> --- Check the merchant.getMaxValue.
> --- Check the result mapping for the 'max' property.
> --- Cause: java.sql.SQLException: Invalid column name
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
> ... 72 more
>
>
> <resultMap id="DistributionResult" class="Distribution">
> <result property="totalProducts" column="TOTAL_PRODUCTS"/>
> <result property="totalAmt" column="TOTAL_AMT"/>
> <result property="merchant.id" column="TERM_ID"/>
> <result property="max" column="{merchantId =
> TERM_ID,#merchants#,#startDate#,#endDate#}" select="merchant.getMaxValue"/>
> </resultMap>
>
> <select id="getDistribution" parameterClass="map"
> resultMap="DistributionResult">
> SELECT COUNT(*) as TOTAL_PRODUCTS
> , SUM(a.amount) as TOTAL_AMT
> , A.TERMID as TERM_ID
> FROM tbl a, tbl b
> WHERE a.termid = b.id
> <iterate property="merchants" open=" and a.SATHCDHACCTKEY in ("
> close = ")" conjunction=",">
> #merchants[]#
> </iterate>
> AND a.dtathdate BETWEEN #startDate# AND #endDate# group by A.TERMID
> </select>
>
> <select id="getMaxVALue" resultClass="int">
> SELECT MAX(subTbl.cnt1) as value from (SELECT COUNT(A.KEY ) as cnt1
> FROM ath a, merchantterminal m WHERE A.SATHTERMID = M.LID and M.LID =
> #merchantId#
> <iterate property="merchants" open=" and a.KEY in (" close = ")"
> conjunction=",">
> #merchants[]#
> </iterate>
> AND a.dtathdate BETWEEN #startDate# AND #endDate# group by
> to_char(a.dtathdate,'YYYYMMDD') ) subTbl
> </select>
>
> Thanks
> Kiran
>
>
>