Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: DISTINCT invalidates the ORDER BY

    Why would I get

    Code:
     ORA-01791: not a SELECTed expression
    When I add the DISTINCT keyword to
    Code:
    SELECT ( NVL(SUBSTR(LAST_NAME,1,25), ' ') || ', ' || NVL(SUBSTR(FIRST_NAME, 1,12), ' ') || ' ' || NVL(SUBSTR(MIDDLE_NAME, 1, 10), ' ')  ) As AGNT_NAME
    FROM ...
    WHERE...
    ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME
    Without the DISTINCT I have no problems.

    The way I was thinking about the problem was that, if any of these fields ever has an index associated with it in the future (none do currently), then be leaving them in their original format in the ORDER BY might influence the optimizer's approach to use that index when running the query.

    -Chuck

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here is the explanation:

    Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

    Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Exactly - you can

    SELECT x,y FROM tablename ORDER BY z

    but you cannot

    SELECT DISTINCT x,y FROM tablename ORDER BY z

    because after applying the DISTINCT, the result cannot contain a "z" column.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Actually, using the x,y,z example makes sense. I didn't think of it that way.
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •