Following is full of my query.
(I modified few symbols cause I'm too cautious :D but I'm sure that this
doesn't make any difference after all.)
(If this makes your guys uncomfortable, let me know it. I'm not familiar
with mailing culture so I may make some impolite mistake...you may
see...I'm toooo cautious...:~( )
public List<CampaignReport> getReportList(){
getSqlMapClientTemplate().queryForList("Campaign.getReportList" );
}
<select id="getReportList" resultClass="CampaignReport" >
WITH cpids as (
SELECT cpid
FROM campaign
WHERE regdttm > '20080301010101'
)
SELECT *
FROM (
SELECT *
FROM campaign
WHERE cpid in (select * from cpids)
) cp
INNER JOIN (
SELECT ca.cpid,
sum( decode( type, 'click', value, 0 ) ) click
, sum( decode( type, 'imp', value, 0 ) ) imp
FROM campaign_aio ca
INNER JOIN a_log al on ca.aioid = al.aioid and datekey = 3 and ca.cpid in
(select * from cpids)
GROUP BY ca.cpid
) cal ON cal.cpid = cp.cpid
Thanks!
Chimin Park
-----Original Message-----
From: Larry Meadors [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2008 6:44 AM
To: user-java@ibatis.apache.org
Subject: Re: How can I use Oracle's "WITH" Clause?
Can you show the full mapped statement please, not just the SQL (start
with <select ...>).
Larry
2008/3/23 박치민 <[EMAIL PROTECTED]>:
>
>
>
>
> Following simple query doesn't work!
>
>
>
> with test as (
>
> select 1 from dual
>
> )
>
> select * from test
>
>
>
> I logged all message from iBatis with log4j.
>
> There are no error message, no result log.
>
>
>
> DEBUG - JakartaCommonsLoggingImpl.debug(27) | {conn-100013} Preparing
> Statement: with test as ( select 1 from dual ) select * from test
>
> DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100014} Executing
> Statement: with test as ( select 1 from dual ) select * from test
>
> DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100014} Parameters:
> []
>
> DEBUG - JakartaCommonsLoggingImpl.debug(27) | {pstm-100014} Types: []
>
>
>
> It just gives no result!
>
>
>
> I tried searching on archives but it didn't work well…
>
>
>
> Is this impossible? Or is there other way I can use this?
>
>
>
> Thanks!
>
>
>
> Chimin Park
>
>
