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 박치민 <cm...@daumcorp.com> on 2008/03/23 10:15:07 UTC

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

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

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

Posted by Chimin Park <cm...@daumcorp.com>.
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 박치민 <cm...@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

> 

>

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

Posted by Jean-Francois Poilpret <jf...@yahoo.fr>.
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 박치민 <cm...@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
>
>

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

Posted by Chimin Park <cm...@daumcorp.com>.
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 박치민 <cm...@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
>
>

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

Posted by Larry Meadors <la...@gmail.com>.
Can you show the full mapped statement please, not just the SQL (start
with <select ...>).

Larry


2008/3/23 박치민 <cm...@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
>
>