Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.fr;
  h=Received:X-YMail-OSG:X-Yahoo-Newman-Property:From:To:References:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:In-reply-to:Thread-Index:X-MimeOLE;
  b=ncBx2wD0PfP7j3KcCd2KKigsJVleogqdnlWsUfhn3Tar4gZjgplifLzhAt1+AHDkd+r1Px7MbhC/dAQCJhUg0fZEri6dLPgqfwbOlqImla/IeZT13QKYGeHM28XlxgzOjXu5S8TmzISi2aA61UkXnwBuThC45fD1QlGg/dC/YSU=
  ;
From: "Jean-Francois Poilpret" <jfpoilpret@yahoo.fr>
To: <user-java@ibatis.apache.org>
References: <000601c88cc6$66ac99e0$3405cda0$@com>
 <a10a823c0803231444t1e4108d8s6742ae2341fabe0a@mail.gmail.com>
 <000201c88d6b$9c6aa130$d53fe390$@com>
Subject: RE: How can I use Oracle's "WITH" Clause?
Date: Mon, 24 Mar 2008 17:57:12 +0700
Message-ID: <000601c88d9d$d8fe8580$1501a8c0@VAIOSZ330>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
In-reply-to: <000201c88d6b$9c6aa130$d53fe390$@com>
Thread-Index: AciNLp/am0cmZr/+ToKCfnl1Ls7gMgAL7hlgAA/RIyA=

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]=20
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=3D"getReportList" resultClass=3D"CampaignReport" >
  WITH cpids as (
SELECT cpid
  FROM campaign
 WHERE regdttm > '20080301010101'
)
SELECT *
  FROM (
SELECT *=20
  FROM campaign=20
 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 =3D al.aioid and datekey =3D 3 and =
ca.cpid in
(select * from cpids)
 GROUP BY ca.cpid
)  cal ON cal.cpid =3D cp.cpid

Thanks!
Chimin Park

-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]=20
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 =EB=B0=95=EC=B9=98=EB=AF=BC <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=E2=80=A6
>
>
>
> Is this impossible? Or is there other way I can use this?
>
>
>
> Thanks!
>
>
>
> Chimin Park
>
>