ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Zatko <mza...@boscovs.com>
Subject Re: fetch first rows only
Date Fri, 03 Nov 2006 05:42:08 GMT
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 <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!
>
>


--
Michael H. Zatko
Enterprise Developer
mzatko@boscovs.com




Mime
View raw message