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 Erik Kron <er...@yahoo.com> on 2006/03/05 02:49:14 UTC
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(SqlMapDaoTemplate.java:205)
at
com.jstore.persistence.sqlmapdao.ItemSqlMapDao.getItemListByCategory(ItemSqlMapDao.java:48)
at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.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.java: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.executeQueryWithCallback(GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: What is wrong with this SQL statement
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
What DB are you using? I've found out the hard way that if you don't use
correct ANSI syntax you can get unexpected results. Try changing the
statement from a join to a inner join like:
FROM (ITEM I inner join PRODUCT P on P.PRODUCTID = I.PRODUCTID)
Or even better get rid of the parenthesis.
Z.
> <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>
>