ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Steve Biondi" <Ste...@schemalogic.com>
Subject RE: What is wrong with this SQL statement
Date Sun, 05 Mar 2006 07:06:23 GMT
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:erik_kron@yahoo.com] 
Sent: Saturday, March 04, 2006 5:49 PM
To: user-java@ibatis.apache.org
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 

Mime
View raw message