RE: How can I use Oracle's "WITH" Clause?

[Jean-Francois Poilpret]

First try to replace "WHERE regdttm > '20080301010101'" with " WHERE regdttm 
> '20080301010101'" because ">" is not allowed in XML.

Cheers

Jean-Francois

-----Original Message-----
From: Chimin Park [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 24, 2008 11:58 AM
To: user-java@ibatis.apache.org; [EMAIL PROTECTED]
Subject: RE: How can I use Oracle's "WITH" Clause?

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
>
>