Why "SELECT DISTINCT ..." is generated?

From: Bill Fan (bil..anscom.com.au)
Date: Wed Mar 31 2004 - 06:06:54 EST

  • Next message: Bill Fan: "why I can't remove the table records this way?"

    Hi,
     
    I'm using the latest Cayenne 1.0.7
     
    I have the following simple table relationship, a category may have many
    issues and an issue may belongs to different categories,
     
    Category -->CategoryIssue<--Issue
     
    With the following code, the "SELECT DISTINT ..." sql statement was
    generated. It caused some problems as some of the columns in the issue table
    is type of text ... [Microsoft][SQLServer 2000 Driver for
    JDBC][SQLServer]The text, ntext, or image data type cannot be selected as
    DISTINCT.
     
     
    ============================================================
      Expression qualifier =
    ExpressionFactory.binaryPathExp(Expression.EQUAL_TO,
                     "categoryissueArray.toCategory.categoryNumber",
                    categoryNumber);

      // if the "Closed" voc's are not to be included...
      if (!isIncludingClosed)
      {
       qualifier = qualifier.andExp( ExpressionFactory.binaryPathExp(
    Expression.NOT_EQUAL_TO,
                     "toIssuestatus.issueStatusDesc",
                     "Closed") );
      }
      
      SelectQuery query = new SelectQuery(Issue.class, qualifier);
         
      Ordering ordering = new Ordering("issueNumber", Ordering.DESC);
      
      query.addOrdering(ordering);

       // set a relatively high logging level, to show the query execution
    progress
       query.setLoggingLevel(Level.WARN);

       List list = ctxt.performQuery(query);
    ============================================================
     
     
    The full sql statement generated is something like this,
     
    SELECT DISTINCT t0.DateModified, t0.DateSubmitted, t0.IssueDescription,
    t0.IssueName, t0.IssueNumber, t0.IssueId....FROM dbo.ISSUE t0,
    dbo.CATEGORYISSUE t1, dbo.CATEGORY t2, dbo.ISSUESTATUS t3 WHERE t0.IssueId =
    t1.IssueId AND t1.CategoryId = t2.CategoryId AND t0.IssueStatusId =
    t3.IssueStatusId AND ((t2.CategoryNumber = ?) AND (t3.IssueStatusDesc <> ?))
    ORDER BY t0.IssueNumber DESC [bind: 3, 'Closed']
     
    I believe that the default sql statement generated should be with DISTINCT
    set to fales unless it is explicitly set via the statement of
     
    query.setDistinct(true);
     
    Any idea?
     
     
    Thanks for help!
     
     
    Bill

    This archive was generated by hypermail 2.0.0 : Wed Mar 31 2004 - 06:05:51 EST