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 Mike Jackson <mi...@gmail.com> on 2006/06/09 01:24:54 UTC
compound conditions
I'm trying to build a select that uses a union of two sql statements
both using the between operator in SQL to check for a date between two
values. I can't get it to work, I keep getting sax exception saying
that I'm missing the end </dynamic> tag. I've checked the whole file
and I've got balanced tags, so I don't get it. I'm attaching the
XML/SQL and I'm hoping that someone can set me straight.
<select id="maxAllocated"
resultClass="com.caiso.irr.db.ibatis.DaoImpl$InlineFloat">
select
max(value) as value
from (
select
max( somevalue ) as value
from
table_1
<dynamic prepend="WHERE">
<isNotNull property="startDate">
<isNotNull prepend="AND" property="endDate">
opr_dt between #startDate# and ( #endDate# - 1/86400 )
</isNotNull>
</isNotNull>
</dynamic>
union
select
max( somevalue ) as value
from
table_2
<dynamic prepend="WHERE">
<isNotNull property="startDate">
<isNotNull prepend="AND" property="endDate">
opr_dt between #startDate# and ( #endDate# - 1/86400 )
</isNotNull>
</isNotNull>
</dynamic>
)
</select>
--mikej
-=-----
mike jackson
minininja@gmail.com
Character Density, n.:
The number of very weird people in the office.
Re: compound conditions
Posted by Mike Jackson <mi...@gmail.com>.
Hmm, I hadn't thought of that. I'll try that out tomorrow when I get to work.
On 6/8/06, Brandon Goodin <br...@gmail.com> wrote:
> I believe it is the result of the '/' character that you are using.
> Try something like...
>
> <isNotNull prepend="AND" property="endDate">
> opr_dt between #startDate# and ( #endDate# -
> 1<![CDATA[/]]>86400 )
> </isNotNull>
>
> On 6/8/06, Mike Jackson <mi...@gmail.com> wrote:
> > I'm trying to build a select that uses a union of two sql statements
> > both using the between operator in SQL to check for a date between two
> > values. I can't get it to work, I keep getting sax exception saying
> > that I'm missing the end </dynamic> tag. I've checked the whole file
> > and I've got balanced tags, so I don't get it. I'm attaching the
> > XML/SQL and I'm hoping that someone can set me straight.
> >
> > <select id="maxAllocated"
> > resultClass="com.caiso.irr.db.ibatis.DaoImpl$InlineFloat">
> > select
> > max(value) as value
> > from (
> > select
> > max( somevalue ) as value
> > from
> > table_1
> > <dynamic prepend="WHERE">
> > <isNotNull property="startDate">
> > <isNotNull prepend="AND" property="endDate">
> > opr_dt between #startDate# and ( #endDate# - 1/86400 )
> > </isNotNull>
> > </isNotNull>
> > </dynamic>
> > union
> > select
> > max( somevalue ) as value
> > from
> > table_2
> > <dynamic prepend="WHERE">
> > <isNotNull property="startDate">
> > <isNotNull prepend="AND" property="endDate">
> > opr_dt between #startDate# and ( #endDate# - 1/86400 )
> > </isNotNull>
> > </isNotNull>
> > </dynamic>
> > )
> > </select>
> >
--mikej
-=-----
mike jackson
minininja@gmail.com
Character Density, n.:
The number of very weird people in the office.
Re: compound conditions
Posted by Brandon Goodin <br...@gmail.com>.
I believe it is the result of the '/' character that you are using.
Try something like...
<isNotNull prepend="AND" property="endDate">
opr_dt between #startDate# and ( #endDate# -
1<![CDATA[/]]>86400 )
</isNotNull>
On 6/8/06, Mike Jackson <mi...@gmail.com> wrote:
> I'm trying to build a select that uses a union of two sql statements
> both using the between operator in SQL to check for a date between two
> values. I can't get it to work, I keep getting sax exception saying
> that I'm missing the end </dynamic> tag. I've checked the whole file
> and I've got balanced tags, so I don't get it. I'm attaching the
> XML/SQL and I'm hoping that someone can set me straight.
>
> <select id="maxAllocated"
> resultClass="com.caiso.irr.db.ibatis.DaoImpl$InlineFloat">
> select
> max(value) as value
> from (
> select
> max( somevalue ) as value
> from
> table_1
> <dynamic prepend="WHERE">
> <isNotNull property="startDate">
> <isNotNull prepend="AND" property="endDate">
> opr_dt between #startDate# and ( #endDate# - 1/86400 )
> </isNotNull>
> </isNotNull>
> </dynamic>
> union
> select
> max( somevalue ) as value
> from
> table_2
> <dynamic prepend="WHERE">
> <isNotNull property="startDate">
> <isNotNull prepend="AND" property="endDate">
> opr_dt between #startDate# and ( #endDate# - 1/86400 )
> </isNotNull>
> </isNotNull>
> </dynamic>
> )
> </select>
>
> --mikej
> -=-----
> mike jackson
> minininja@gmail.com
>
> Character Density, n.:
> The number of very weird people in the office.
>