ibatis中能否定义临时表？或者帮我改下下面的代码
根据拆分一行变多行我写了如下的sql,具体请见：http://topic.csdn.net/u/20120110/19/501c0c98-2c5d-43a4-88a3-ac5c220a8d3b.html?seed=608344288&r=77257274#r_77257274
因此我不得不创建临时表，因为把查询字段加条件一个一个并连那样sql会很长，但用临时表后ibatis中用如下的代码加进去好像会报错，请高手帮忙解决下或者看看还有没更好的方法，谢谢！！！！！
<select id="IbatisOrderDetailDAO.queryBizPackageAndDetail" parameterClass="java.util.Map" resultMap="extendsResultMap">
WITH maco AS (
select * from (select pack.package_id,pack.PACKAGE_NUMBER,pack.order_id,b.order_code,b.pay_type,pack.deliver_name,pack.deliver_no,
packde.item_name,packde.sku_code,packde.quantity,packde.return_quantity,packde.EXCHANGE_QUANTITY,pack.gmt_modified,
(select od.item_codes from x_tm_order_detail od where od.sku_code=packde.sku_code and rownum=1) item_codes,
addr.full_name,addr.phone,b.buyer_nick
from X_TM_PACKAGE pack
left join x_tm_package_detail packde on pack.package_id=packde.package_id
inner join X_TM_ORDER b on pack.order_id=b.order_id
inner join x_tm_deliver_addr addr on addr.id=b.order_deliver_addr_id
and pack.status=0 and pack.LOGISTICS_STATUS=2 order by pack.package_id)BTA where 1=1
<isNotEmpty property="queryDO.orderCode" prepend="AND">
BTA.ORDER_CODE like '%'||#queryDO.orderCode#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.packageNumber">
and BTA.PACKAGE_NUMBER like '%'||#queryDO.packageNumber#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.deliverNo" prepend="AND">
BTA.deliver_no like '%'||#queryDO.deliverNo#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.deliverName" prepend="AND">
BTA.deliver_name like '%'||#queryDO.deliverName#||'%'
</isNotEmpty>
<isEqual property="queryDO.returnAndExchangeStatus" compareValue="0">

</isEqual>
<!-- 退 -->
<isEqual property="queryDO.returnAndExchangeStatus" compareValue="1">
and (BTA.EXCHANGE_QUANTITY=0 and BTA.return_quantity > 0)
</isEqual>
<!-- 换-->
<isEqual property="queryDO.returnAndExchangeStatus" compareValue="2">
and (BTA.EXCHANGE_QUANTITY > 0 and BTA.return_quantity=0)
</isEqual>
<!-- 拒收功能没做后期完善
<isEqual property="returnAndExchangeStatus" compareValue="3">
(packde.EXCHANGE_QUANTITY=0 and packde.return_quantity=0)
</isEqual> -->
<isNotEmpty property="queryDO.beginTime">
<isNotEmpty property="queryDO.endTime">
and (BTA.gmt_modified > #queryDO.beginTime# and BTA.gmt_modified < #queryDO.endTime#)
</isNotEmpty>
</isNotEmpty>
<isNotEmpty property="queryDO.skuCode" prepend="AND">
BTA.sku_code like '%'||#queryDO.skuCode#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.itemCodes" prepend="AND">
BTA.item_codes like '%'||#queryDO.itemCodes#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.itemName" prepend="AND">
BTA.item_name like '%'||#queryDO.itemName#||'%'
</isNotEmpty>

<isNotEmpty property="queryDO.payType">
<isNotEqual property="queryDO.payType" compareValue="-1">
and BTA.pay_type = #queryDO.payType#
</isNotEqual>
</isNotEmpty>
<isNotEmpty property="queryDO.fullName" prepend="AND">
BTA.full_name like '%'||#queryDO.fullName#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.phone" prepend="AND">
BTA.phone like '%'||#queryDO.phone#||'%'
</isNotEmpty>
<isNotEmpty property="queryDO.buyerNick" prepend="AND">
BTA.buyer_nick like '%'||#queryDO.buyerNick#||'%'
</isNotEmpty>
<isNotEmpty property="ids" prepend="and">
BTA.package_id
<iterate prepend="in" property="ids" open="(" close=")" conjunction="," >
#ids[]#
</iterate>
</isNotEmpty>
)

SELECT *
FROM
(
SELECT A.*,ROWNUM rn FROM(
select package_id,
PACKAGE_NUMBER,
order_id,
order_code,
pay_type,
deliver_name,
deliver_no,
item_name,
sku_code,
quantity,
return_quantity,
EXCHANGE_QUANTITY,
REJECTION_QUANTITY,
gmt_modified,
item_codes,
full_name,
phone,
buyer_nick from maco where return_quantity<>0
union all
select package_id,
PACKAGE_NUMBER,
order_id,
order_code,
pay_type,
deliver_name,
deliver_no,
item_name,
sku_code,
quantity,
return_quantity,
EXCHANGE_QUANTITY,
REJECTION_QUANTITY,
gmt_modified,
item_codes,
full_name,
phone,
buyer_nick from maco where EXCHANGE_QUANTITY<>0
union all
select package_id,
PACKAGE_NUMBER,
order_id,
order_code,
pay_type,
deliver_name,
deliver_no,
item_name,
sku_code,
quantity,

return_quantity,
EXCHANGE_QUANTITY,
REJECTION_QUANTITY,
gmt_modified,
item_codes,
full_name,
phone,
buyer_nick from maco where REJECTION_QUANTITY<>0
union all
select package_id,
PACKAGE_NUMBER,
order_id,
order_code,
pay_type,
deliver_name,
deliver_no,
item_name,
sku_code,
quantity,
return_quantity,
EXCHANGE_QUANTITY,
REJECTION_QUANTITY,
gmt_modified,
item_codes,
full_name,
phone,
buyer_nick from maco where (quantity-return_quantity-EXCHANGE_QUANTITY-REJECTION_QUANTITY)>0
) A
WHERE ROWNUM < #endRow#
)WHERE rn > #beginRow#
</select>


[解决办法]
please format your sql code like this

SQL code

XXX
[解决办法]
SQL code
select 包裹id,订单id,包裹编码,'己退货' AS 售后状态 from ( SELECT   a.*,b.退换数量,b.换货数量,b.拒收数量               FROM     x_tm_package a                        LEFT JOIN x_tm_package_detail b ON a.包裹id = b.包裹id)maco where 退换数量=1union select 包裹id,订单id,包裹编码,'己换货' from ( SELECT   a.*,b.退换数量,b.换货数量,b.拒收数量               FROM     x_tm_package a                        LEFT JOIN x_tm_package_detail b ON a.包裹id = b.包裹id)maco where 换货数量=1union select 包裹id,订单id,包裹编码,'己拒收' from ( SELECT   a.*,b.退换数量,b.换货数量,b.拒收数量               FROM     x_tm_package a                        LEFT JOIN x_tm_package_detail b ON a.包裹id = b.包裹id)maco where 拒收数量=1union select 包裹id,订单id,包裹编码,'正常' from ( SELECT   a.*,b.退换数量,b.换货数量,b.拒收数量               FROM     x_tm_package a                        LEFT JOIN x_tm_package_detail b ON a.包裹id = b.包裹id)maco where 退换数量+换货数量+拒收数量=0order by 1
[解决办法]
同一楼上  我开始也跟LZ一样的想法 不过把from 后的换成select  就OK了