ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Zatko <mza...@boscovs.com>
Subject fetch first rows only
Date Thu, 02 Nov 2006 22:13:53 GMT
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