A classic EJB Left Join and Order By clause problem

[app4you]

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

[abuayyub]

The hibernate dock has this concerning order by

The list returned by a query may be ordered by any property of a returned class or components:

regards abuayyub

[app4you]

Well I found the solution to my problem by updating to Oracle to 10g since any version below 10g does not support the ORDER BY column that is not a part of the SELECT statement. Also just a thought out loud, it seems that a lot of posts don't have any answer at all. Folks please help out if you know the answer.

Thanks,
John

[fhh]

Well I found the solution to my problem by updating to Oracle to 10g since any version below 10g does not support the ORDER BY column that is not a part of the SELECT statement.

I'm not sure what you're error msg said but I don't believe this was the problem. I just tried

SELECT columnA FROM tableA order by columnB

and it works flawlessly. Of course, it does!

Regards

fhh