ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Maves" <nathan.ma...@gmail.com>
Subject Re: fetch first rows only
Date Fri, 03 Nov 2006 01:37:22 GMT
try $rows$

On 11/2/06, Michael Zatko <mzatko@boscovs.com> wrote:
>
> Hi, I am having an issue substituting a variable in to a "fetch first 10
> rows only" clause.
>
> The following is my sqlmap:
>
> <select id="getTopRegisteredItems" resultClass="
> com.boscovs.greports.models.TopRegisteredResult"
>  parameterClass="com.boscovs.greports.models.TopRegisteredReport">
>  select vendor, itemname, color, itemsize as size, sku, wantSum as wants
>  from
>  (
>  select part3.*,imxl.itemname, imxl.itemsize, imxl.color from
>  (
>  select part2.*,b.brandname as vendor from
>  (
>  select part1.*,coalesce(im.brandid,-999) as vendorid from
>  (
>  select gion.sku, re.retailerid, sum(gion.WANT) as wantSum
>  from GIFTITMS_ON_SYS gion
>  inner join REGISTRY_EVENT re
>  on
>  gion.custcode = re.CUSTCODE
>  and gion.RETAILERID = re.RETAILERID
>  where gion.want &gt; 1
>  and date(re.REGISTER_DATE) between #startDate# and #endDate#
>  group by gion.sku, re.retailerid
>  order by wantSum desc
>  fetch first #rows# rows only) as part1
>
>
>  inner join itemmaster im
>  on
>  part1.sku=im.itemkey
>  and part1.retailerid = im.RETAILERID) as part2
>  left join BRAND b
>  on
>  part2.vendorid = b.BRANDID) as part3
>  inner join ITEMMASTERXLATE imxl
>  on part3.sku = imxl.itemkey
>  and part3.retailerid = imxl.retailerid) as part4
> </select>
>
>
> This is TopRegisteredReport:
>
> public class TopRegisteredReport {
>
> private Date startDate;
> private Date endDate;
> private Integer startMonth;
> private Integer startYear;
> private Integer endYear;
> private Integer endMonth;
> private Integer rows;
> private String dateType;
> private String outputFormat;
> private Integer individualStoreNumber;
>
> ...getters/setters
> }
>
>
> The error I get:
>
> org.springframework.web.util.NestedServletException: Request processing
> failed; nested exception is
> org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation;
> bad SQL grammar []; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in sql.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getTopRegisteredItems-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104,
> SQLSTATE: 42601, SQLERRMC: ?;desc  fetch first;<space>
> Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104,
> SQLSTATE: 42601, SQLERRMC: ?;desc  fetch first;<space>
> org.springframework.web.servlet.FrameworkServlet.processRequest(
> FrameworkServlet.java:408)
> org.springframework.web.servlet.FrameworkServlet.doPost(
> FrameworkServlet.java:360)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
> javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
>
>
>
>
> SQLSTATE 42601 is....  A character, token, or clause is invalid or
> missing.
>
>
>
> This statement runs fine with the* fetch first 50 rows only* hardcoded.
> How do I get sqlmaps to substitute a variable for this? Thanks!
>
>

Mime
View raw message