DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: 1)ORA-00904: "E"."AVG" invalid identifier2)How 2 See Savepoints

  1. #1
    Join Date
    Jul 2009
    Posts
    10

    1)ORA-00904: "E"."AVG" invalid identifier2)How 2 See Savepoints

    1)Friends tell me the reason why this query is giving error
    SELECT e.avg(sal) FROM emp e;
    Error: ORA-00904: "E"."AVG" invalid identifier

    If i give the query as
    SELECT avg(sal) FROM emp e;
    it works why is this so as i have given the alias name for emp as e than i need to do e.avg(sal)

    2)Friends as per my knowledge in the absence of GROUP BY clause HAVING CLAUSE works as WHERE CLAUSE then why is this statement not executing.
    SELECT sal FROM emp HAVING sal>3000;

    Error: ORA-00979: Not a Group by expression

    3)Savepoints
    Friends how can i know what are the save points i have given;
    example->
    SQL>savepoint a;

    SQL>savepoint b;

    SQL>savepoint c;

    And if i want to know what are d savepoints(incase i forgot) i have given what should i do?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    1) Syntax error; as you already figure out but probably didn't realize yet AVG() is not a valid column on emp table.
    2) Syntax error; HAVING clause works on groups created by GROUP BY clause.
    3) What?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2009
    Posts
    10
    "Avg() is not a valid column on emp table"
    PAVB can you please explain me this

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by lucky10101 View Post
    "Avg() is not a valid column on emp table"
    PAVB can you please explain me this
    Your syntax implies AVG is a column of EMP table -which is not; that's why you are getting ORA-00904: "E"."AVG" invalid identifier

    You do not qualify a function, you qualify a column name...

    e.avg(sal) <<< Wrong
    avg(e.sal) <<< Correct
    Last edited by PAVB; 07-13-2009 at 07:33 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width