1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904 error

Discussion in 'SQL PL/SQL' started by adjunctRelflex, Dec 12, 2011.

  1. I cant seem to get aliases working here is the code
    Code (Text):
    SELECT owner,COUNT(object_type) AS "Most Objects" FROM dba_objects
    GROUP BY owner
    HAVING "Most Objects" = max((SELECT DISTINCT object_type FROM dba_objects
    when i try to run the script i get the error ORA-00904: "Most Objects": invalid identifier
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    What exactly is this query supposed to return?

    max((SELECT DISTINCT object_type FROM dba_objects))

    does not return a number, it returns the greatest object type name based upon the ascii codes for the characters. Also, you cannot use an alias as you're trying to do as Oracle cannot resolve it within the group by clause. You need to return a number to compare the count() to so even if the alias was resolvable you'd receive an error for a type mismatch.