[jboss-user] [EJB 3.0] - A classic EJB Left Join and Order By clause problem

[app4you@yahoo.com]

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#3972116

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3972116