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 Carfield Yim <ca...@carfield.com.hk> on 2007/01/16 12:44:53 UTC
Problem of using union with ibatis
I already named 2 column in same position have same name, but ibatis
still complaint about column not found. How can I do?
Exception:
org.postgresql.util.PSQLException: The column name rate was not found
in this ResultSet.
XML:
<resultMap id="orderResult" class="scommon.ibatis.BuySellOrder">
<result property="orderId" column="orderid"/>
<result property="orderRequestId" column="clientorderid"/>
<result property="accountId" column="accountid"/>
<result property="sideStr" column="side"/>
<result property="symbol" column="product"/>
<result property="stopLossValue" column="stopLoss"/>
<result property="takeProfitValue" column="takeProfit"/>
<result property="trailingStop" column="trailingstop"/>
<result property="marginReq" column="margin_req"/>
<result property="lastUpdated" column="last_updated"/>
<result property="expiryTime" column="expiry"/>
<result property="limitRateValue" column="limit_rate"/>
<result property="leverage" column="leverage"/>
<result property="revision" column="rev"/>
<result property="expiryType" column="expiry_type"/>
<result property="orderType" column="ordertype"/>
<result property="orderStatus" column="status"/>
<result property="pipCost" column="costperpip"/>
<result property="pnlDollars" column="pnldollars"/>
<result property="swapTotal" column="swaptotal"/>
<result property="commission" column="commission"/>
<result property="amount" column="amount"/>
<result property="rate" column="rate"/>
<result property="timestamp" column="timestamp"/>
</resultMap>
<sql id="buySellSearchFragment">
select t.* from (
select orderid, clientorderid, accountid, side,
product, stopLoss, takeProfit, trailingstop, margin_req,
last_updated, swaptotal, commission,
expiry, limit_rate, leverage, rev, expiry_type, ordertype, status,
costperpip, pnldollars,
when_filled as timestamp, filled_rate as rate, filled_amount as
amount from orders
union
select orderid, clientorderid, accountid, case side when 'B' then
'S' when 'S' then 'B' end,
product, stopLoss, takeProfit, trailingstop, margin_req,
last_updated, swaptotal, commission,
expiry, limit_rate, leverage, rev, expiry_type, ordertype, status,
costperpip, pnldollars,
when_closed as timestamp, closed_rate as rate, amount as amount from orders
where when_closed is not null
) t INNER JOIN orders on t.orderid = orders.orderid INNER JOIN
accounts on orders.accountID = accounts.accountID
LEFT JOIN comments ON orders.orderid=comments.orderid WHERE
accounts.accountID > 0
<include refid="searchshare"/>
</sql>
Re: Problem of using union with ibatis
Posted by Carfield Yim <ca...@gmail.com>.
> I'd suggest running the query in a database tool to get a better
> explanation of the problem with the SQL statement.
>
Have tried that, look like the column are correctly displayed
> It may be the side column - i see a case in the second part that looks
> like it's missing an AS.
>
Yes, this is missing, but after I add AS side, it still show the error...
Re: Problem of using union with ibatis
Posted by Larry Meadors <lm...@apache.org>.
That's really not an iBATIS err you are seeing, it's a PostgreSQL error:
org.postgresql.util.PSQLException
I'd suggest running the query in a database tool to get a better
explanation of the problem with the SQL statement.
It may be the side column - i see a case in the second part that looks
like it's missing an AS.
Larry
On 1/16/07, Carfield Yim <ca...@carfield.com.hk> wrote:
> I already named 2 column in same position have same name, but ibatis
> still complaint about column not found. How can I do?
>
> Exception:
> org.postgresql.util.PSQLException: The column name rate was not found
> in this ResultSet.
>
> XML:
>
> <resultMap id="orderResult" class="scommon.ibatis.BuySellOrder">
> <result property="orderId" column="orderid"/>
> <result property="orderRequestId" column="clientorderid"/>
> <result property="accountId" column="accountid"/>
> <result property="sideStr" column="side"/>
> <result property="symbol" column="product"/>
> <result property="stopLossValue" column="stopLoss"/>
> <result property="takeProfitValue" column="takeProfit"/>
> <result property="trailingStop" column="trailingstop"/>
> <result property="marginReq" column="margin_req"/>
> <result property="lastUpdated" column="last_updated"/>
> <result property="expiryTime" column="expiry"/>
> <result property="limitRateValue" column="limit_rate"/>
> <result property="leverage" column="leverage"/>
> <result property="revision" column="rev"/>
> <result property="expiryType" column="expiry_type"/>
> <result property="orderType" column="ordertype"/>
> <result property="orderStatus" column="status"/>
> <result property="pipCost" column="costperpip"/>
> <result property="pnlDollars" column="pnldollars"/>
> <result property="swapTotal" column="swaptotal"/>
> <result property="commission" column="commission"/>
>
> <result property="amount" column="amount"/>
> <result property="rate" column="rate"/>
> <result property="timestamp" column="timestamp"/>
> </resultMap>
>
> <sql id="buySellSearchFragment">
> select t.* from (
> select orderid, clientorderid, accountid, side,
> product, stopLoss, takeProfit, trailingstop, margin_req,
> last_updated, swaptotal, commission,
> expiry, limit_rate, leverage, rev, expiry_type, ordertype, status,
> costperpip, pnldollars,
> when_filled as timestamp, filled_rate as rate, filled_amount as
> amount from orders
> union
> select orderid, clientorderid, accountid, case side when 'B' then
> 'S' when 'S' then 'B' end,
> product, stopLoss, takeProfit, trailingstop, margin_req,
> last_updated, swaptotal, commission,
> expiry, limit_rate, leverage, rev, expiry_type, ordertype, status,
> costperpip, pnldollars,
> when_closed as timestamp, closed_rate as rate, amount as amount from orders
> where when_closed is not null
> ) t INNER JOIN orders on t.orderid = orders.orderid INNER JOIN
> accounts on orders.accountID = accounts.accountID
> LEFT JOIN comments ON orders.orderid=comments.orderid WHERE
> accounts.accountID > 0
> <include refid="searchshare"/>
> </sql>
>