Hi Eric, It looks like the RDBMS is expecting a subquery when it encounters the parentheses right after the FROM keyword:
e.g. from ( select * from item i inner join product p on p.productid = i.productid ) as X It may have been the author's intent to use the parens to simply highlight the join condition, which would not imply a subquery: from item i inner join product p on ( p.productid = i.productid ) Moving the left paren or removing them altogether should make it work correctly. Steve -----Original Message----- From: Erik Kron [mailto:[EMAIL PROTECTED] Sent: Saturday, March 04, 2006 5:49 PM To: [email protected] Subject: What is wrong with this SQL statement Can somebody please tell me what is wrong with this SQL statement. <select id="getItemListByCategory" resultClass="item" parameterClass="string" cacheModel="itemCache"> SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = #value# </select> I am playing around with the latest jpetstore code. Below is part of the exception that was thrown. ///////////////////////// Caused by: com.ibatis.dao.client.DaoException: Failed to queryForList - id [getItemListByCategory], parameterObject [FISH]. Cause: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in com/jstore/persistence/sqlmapdao/sql/Item.xml. --- The error occurred while executing query. --- Check the SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? . --- Check the SQL Statement (preparation failed). --- Cause: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] Caused by: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] Caused by: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in com/jstore/persistence/sqlmapdao/sql/Item.xml. --- The error occurred while executing query. --- Check the SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? . --- Check the SQL Statement (preparation failed). --- Cause: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] Caused by: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] at com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForList(SqlMapDaoT emplate.java:205) at com.jstore.persistence.sqlmapdao.ItemSqlMapDao.getItemListByCategory(Ite mSqlMapDao.java:48) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav a:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72) at $Proxy19.getItemListByCategory(Unknown Source) at com.jstore.service.CatalogService.getItemListByCategory(CatalogService.j ava:39) at com.jstore.presentation.CatalogBean.viewItemsInCategory(CatalogBean.java :135) at com.jstore.presentation.CatalogBean.viewCategory(CatalogBean.java:129) ... 31 more Caused by: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in com/jstore/persistence/sqlmapdao/sql/Item.xml. --- The error occurred while executing query. --- Check the SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? . --- Check the SQL Statement (preparation failed). --- Cause: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] Caused by: java.sql.SQLException: Unexpected token ITEM, requires SELECT in statement [ SELECT ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS "product.productId", NAME AS "product.name", DESCN AS "product.description", CATEGORY AS "product.categoryId", STATUS, ATTR1 AS attribute1, ATTR2 AS attribute2, ATTR3 AS attribute3, ATTR4 AS attribute4, ATTR5 AS attribute5 FROM (ITEM I join PRODUCT P on P.PRODUCTID = I.PRODUCTID) where P.CATEGORY = ? ] at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery WithCallback(GeneralStatement.java:185) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery ForList(GeneralStatement.java:123) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap ExecutorDelegate.java:610) __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
