2 Replies Latest reply on Mar 31, 2003 3:45 AM by Laurent Broudoux

    SQL generated from EJB-QL with OR statements

    Jochen Cordes Novice

      Hi all !

      I'm trying to build a search feature for a product-catalogue. The user should be able to query for an article-name OR a manufacturer OR an article-number (not exclusive or's, no multiple resulting articles). Therefore I've setup these (bidirectional-)relationships:

      Article-ArticleDescription: 1-Many (multiple languages)
      Article-Manufacturer: Many-1

      My EJB-QL looks like this:

      SELECT DISTINCT OBJECT(a) FROM Article a, IN (a.articleDescription) ad WHERE (ad.descriptionShort = ?1 AND ad.language = ?3) OR (a.supplierAid=?2) OR (a.manufacturer.manufacturerName = ?4)

      That's the generated SQL:

      SELECT DISTINCT t0_a.id FROM Article t0_a, Article_Description t1_ad, Manufacturer t2_a_manufacturer WHERE ((t1_ad.description_short = ? AND t1_ad.language = ?) OR (t0_a.supplier_aid = ?) OR (t2_a_manufacturer.manufacturer_name = ?)) AND (t0_a.manufacturer_id=t2_a_manufacturer.id AND t0_a.id=t1_ad.article_id);

      I'd expected this SQL-statement:

      SELECT DISTINCT t0_a.id FROM Article t0_a, Article_Description t1_ad, Manufacturer t2_a_manufacturer WHERE
      ((t1_ad.description_short = ? AND t1_ad.language = ? AND t0_a.id=t1_ad.article_id) OR (t0_a.supplier_aid = ?) OR (t2_a_manufacturer.manufacturer_name = ? AND t0_a.manufacturer_id=t2_a_manufacturer.id));

      What did I do wrong or is it a bug ?

      TIA,

      Jochen.