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 "Vu, Thai" <Th...@parsons.com> on 2006/02/27 20:59:53 UTC
Help me with this please
I cannot make the ORDER BY clause work.
public class RequirementsSummaryCondBean {
private String majcom;
private Integer budgetProject;
private String location;
private String round;
private String sortBy;
// getter & setter methods here
}
<resultMap id="select-requirement-result"
class="com.parsons.bmt.financial.beans.RequirementsSummaryBean">
<result property="projectref" column="PROJECTREF" />
<result property="projectid" column="PROJECTID" />
<result property="recname" column="RECNAME" />
<result property="location" column="LOCATION_X" />
<result property="oac" column="OAC" />
<result property="majcom" column="MAJCOM" />
<result property="budgetProject" column="BUDGETPROJECT" />
<result property="baan" column="BAAN" />
<result property="subaction" column="SUBACTION" />
<result property="title" column="TITLE" />
<result property="required" column="REQUIRED" />
<result property="funded" column="FUNDED" />
<result property="savedDeltas" column="SAVED_DELTAS" />
</resultMap>
<select id="getRequirements"
parameterClass="com.parsons.bmt.financial.beans.RequirementsSummaryCondB
ean"
resultMap="select-requirement-result">
<![CDATA[
SELECT p.projectref, p.projectid,
(SELECT base4digit || ' - ' || base FROM gl_base WHERE
baseid = p.base) AS recname,
(SELECT location FROM gl_location WHERE code = p.location)
AS location_X,
(SELECT oac FROM gl_execcommand WHERE majcom = p.majcom) AS
oac,
majcom, budgetproject, baan, subaction, title,
(SELECT NVL(SUM(NVL(y2006, 0) + NVL(y2007, 0) + NVL(y2008,
0) + NVL(y2009, 0) + NVL(y2010, 0) + NVL(y2011, 0)), 0)
FROM rm_funding WHERE projectref = p.projectref) AS
required,
NVL(funded, 0) AS funded, saved_deltas
FROM rm_project p
]]>
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="majcom">
majcom = #majcom#
</isNotNull>
<isNotNull prepend="AND" property="budgetProject">
p.budgetproject = #budgetProject#
</isNotNull>
<isNotNull prepend="AND" property="location">
p.location = #location#
</isNotNull>
<isNotNull prepend="AND" property="round">
p.round = #round#
</isNotNull>
</dynamic>
<dynamic prepend="ORDER BY">
<isNotNull prepend=", " property="sortBy">
#sortBy#
</isNotNull>
</dynamic>
</select>
sqlMap.queryForList("getRequirements", requirementsSummaryCondBean);
It seems to me that the ORDER BY clause doesn't exist because the result
doesn't change (I'm sure that the `sortBy' property in the
requirementsSummaryCondBean is not null). I tried that case and this
case (without the <dynamic> tag):
<isNotNull property="sortBy">
ORDER BY #sortBy#
</isNotNull>
And both of them don't work.
Re: Help me with this please
Posted by Henry Lu <zh...@umich.edu>.
>><isNotNull property="sortBy">
>> ORDER BY #sortBy#
>></isNotNull>
replace this with following:
<isNotNull property="sortBy">
ORDER BY $sortBy$
</isNotNull>
-Henry
Vu, Thai wrote:
>I cannot make the ORDER BY clause work.
>
>public class RequirementsSummaryCondBean {
> private String majcom;
> private Integer budgetProject;
> private String location;
> private String round;
> private String sortBy;
> // getter & setter methods here
>}
>
><resultMap id="select-requirement-result"
>
>class="com.parsons.bmt.financial.beans.RequirementsSummaryBean">
> <result property="projectref" column="PROJECTREF" />
> <result property="projectid" column="PROJECTID" />
> <result property="recname" column="RECNAME" />
> <result property="location" column="LOCATION_X" />
> <result property="oac" column="OAC" />
> <result property="majcom" column="MAJCOM" />
> <result property="budgetProject" column="BUDGETPROJECT" />
> <result property="baan" column="BAAN" />
> <result property="subaction" column="SUBACTION" />
> <result property="title" column="TITLE" />
> <result property="required" column="REQUIRED" />
> <result property="funded" column="FUNDED" />
> <result property="savedDeltas" column="SAVED_DELTAS" />
> </resultMap>
> <select id="getRequirements"
>
>parameterClass="com.parsons.bmt.financial.beans.RequirementsSummaryCondB
>ean"
> resultMap="select-requirement-result">
> <![CDATA[
> SELECT p.projectref, p.projectid,
> (SELECT base4digit || ' - ' || base FROM gl_base WHERE
>baseid = p.base) AS recname,
> (SELECT location FROM gl_location WHERE code = p.location)
>AS location_X,
> (SELECT oac FROM gl_execcommand WHERE majcom = p.majcom) AS
>oac,
> majcom, budgetproject, baan, subaction, title,
> (SELECT NVL(SUM(NVL(y2006, 0) + NVL(y2007, 0) + NVL(y2008,
>0) + NVL(y2009, 0) + NVL(y2010, 0) + NVL(y2011, 0)), 0)
> FROM rm_funding WHERE projectref = p.projectref) AS
>required,
> NVL(funded, 0) AS funded, saved_deltas
> FROM rm_project p
> ]]>
> <dynamic prepend="WHERE">
> <isNotNull prepend="AND" property="majcom">
> majcom = #majcom#
> </isNotNull>
> <isNotNull prepend="AND" property="budgetProject">
> p.budgetproject = #budgetProject#
> </isNotNull>
> <isNotNull prepend="AND" property="location">
> p.location = #location#
> </isNotNull>
> <isNotNull prepend="AND" property="round">
> p.round = #round#
> </isNotNull>
> </dynamic>
> <dynamic prepend="ORDER BY">
> <isNotNull prepend=", " property="sortBy">
> #sortBy#
> </isNotNull>
> </dynamic>
> </select>
>
>sqlMap.queryForList("getRequirements", requirementsSummaryCondBean);
>
>It seems to me that the ORDER BY clause doesn't exist because the result
>doesn't change (I'm sure that the `sortBy' property in the
>requirementsSummaryCondBean is not null). I tried that case and this
>case (without the <dynamic> tag):
>
><isNotNull property="sortBy">
> ORDER BY #sortBy#
></isNotNull>
>
>And both of them don't work.
>
>
>
>
>
>