I have the following code :

.....
String sql = "from TABLE1 a where a.ban in " +
"( select c.ban from TABLE2 c where c.id = :id )";

sess.createQuery(sql).setLong("id", subsId.longValue()).list();

........

But when I run I get an error in parsing the sql saying an "in" is required.

Can you tell me what is wrong with this hibernate query?

Mike

Hi,
Change your hiberante config file, so that you can see the SQL statements. and you can find if there is problem in sql statement.

change the above one and try this one
List results = session.createQuery("from Category c " +
"where :item in (from c.items)").setEntity("item", item).list();

Let us know if it works and also send the SQL statement generated in the log/console.

The following error appears :

ERROR - net.sf.hibernate.QueryException: in expected: a [ from ACCOUNTS
a where a.ban in ( select c.ban from CHEV_DISTRIBUTIONS c where c.subscriber_id
= :id )]
net.sf.hibernate.QueryException: in expected: a [from ACCOUNTS a where
a.ban in ( select c.ban from CHEV_DISTRIBUTIONS c where c.subscriber_id = :id )]

at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
.........

It' s parsing error, and I do not know why. Isuppose it's something about the joining.

It seems that your database doesn't support subselects.

The database used is Oracle. I doubt that it does not support subselects.
I manage to accomplish the select in two steps :

String query = "select ban from CHEV_DISTRIBUTIONS where subscriber_id=" + subsId;
rs = stm.executeQuery(query);

List bans = new ArrayList();
while(rs.next()) {
long ban = rs.getLong(1);
bans.add(new Long(ban));
}
Long[] array = new Long[bans.size()];
array = (Long[])bans.toArray(array);

Criteria crit = sess.createCriteria(Accounts.class).add(Expression.in("ban", array));
List result = crit.list();

I know that must be a simpler and better way but I did not find it. It will remain as a TODO task. If someone has an ideea how to accomplish this in only one select please tell me.

Mike