[jboss-user] [EJB 3.0] - A classic EJB Left Join and Order By clause problem
Good day,
I am using Oracle 9i and I would like to apply the ORDER BY on the Customer.state when
querying the Order class.
1. Here are the entities:
@Entity
@Table(name="ORDERS")
public class Order implements Serializable{
...
@ManyToOne
@JoinColumn(name="USERID")
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
@ManyToOne
@JoinColumn(name="PRODUCTID")
public Customer getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
...
}
@Entity
public class Customer implements Serializable{
String address1;
String address2;
String city;
String state;
String zip;
...
}
@Entity
public class Product implements Serializable{
int productid;
String productName;
...
}
2. Here is the query that I would like to get it working so that the result will be a list
of the Order entity bean where I can get all the
products and the customers for a report listing. Please notice the DISTINCT and the ORDER
BY clause on the Order's customer state property.
.createQuery("select distinct order
from Order order
left join order.customer customer
order by customer.state");
3. All worked fine on the MySQL DB server, but Oracle threw an error saying something like
" not SELECTed statement " which
means that the order by column must be a part of the select statement.
4. Question: how can we go about to make the above statement work so that I can get all
the results?
Your help is greatly appreciated.
Thank you,
John
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3972116#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...