ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chimin Park" <cmp...@daumcorp.com>
Subject RE: How can I use Oracle's "WITH" Clause?
Date Tue, 25 Mar 2008 04:47:09 GMT
Jean-Francois, Thank you for your advice!

 

That was my mistake!

 

I rewrote sql-map and DAO class...

 

Anyway!

What I really want to know is that...

 

Is there anybody who used Oracle’s “WITH-clause” in iBatis???

And how? Please~~~

 

Thanks.

Depressed Chimin.

 

--- sql-map(namespace=”Campaign”) --

<select id="getReportList" resultClass="CampaignReport" >

  WITH cpids as (

SELECT cpid

  FROM vms_vr_campaign

)

SELECT *

  FROM (

SELECT * 

  FROM vms_vr_campaign 

 WHERE cpid in (select * from cpids)

)   cp

  </select>

 

-- DAO ---

       public List<CampaignReport> getReportList(  ){

             

              return (List<CampaignReport>)getSqlMapClientTemplate().queryForList("Campaign.getReportList"
);

       }

 

 

-----Original Message-----
From: Jean-Francois Poilpret [mailto:jfpoilpret@yahoo.fr] 
Sent: Monday, March 24, 2008 7:57 PM
To: user-java@ibatis.apache.org
Subject: RE: How can I use Oracle's "WITH" Clause?

 

 

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

 

Cheers

 

Jean-Francois

 

-----Original Message-----

From: Chimin Park [mailto:cmpark@daumcorp.com] 

Sent: Monday, March 24, 2008 11:58 AM

To: user-java@ibatis.apache.org; larry.meadors@gmail.com

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:larry.meadors@gmail.com] 

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 박치민 <cmpark@daumcorp.com>:

> 

> 

> 

> 

> 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

> 

> 

 


Mime
View raw message