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 Michael Zatko <mz...@boscovs.com> on 2006/11/02 23:13:53 UTC
fetch first rows only
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 > 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!
Re: fetch first rows only
Posted by Michael Zatko <mz...@boscovs.com>.
Thank you, sir and sorry for the email malfunction. ;-P
On Nov 2, 2006, at 8:37 PM, Nathan Maves wrote:
> try $rows$
>
> On 11/2/06, Michael Zatko <mz...@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 > 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!
>
>
--
Michael H. Zatko
Enterprise Developer
mzatko@boscovs.com
Re: fetch first rows only
Posted by Nathan Maves <na...@gmail.com>.
try $rows$
On 11/2/06, Michael Zatko <mz...@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 > 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!
>
>