3 Replies Latest reply on Sep 19, 2006 9:42 AM by fhh

    A classic EJB Left Join and Order By clause problem

    John Newbie

      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