Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Posts
    20

    Unanswered: ORA-00904: invalid column name

    Please dont shoot me if I am missing something very basic.

    I have Oracle 8i and I am getting the folllowing error with the query:

    Select a.time as T from a group by T;

    ORA-00904: invalid column name

    What is wrong with this? Can I not use name 'T' in group by expression?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select keyword from V_$RESERVED_WORDS where keyword like '%TIME%'
    SQL> /

    KEYWORD
    ----------------------------------------------------------------
    CONNECT_TIME
    CURRENT_TIME
    CURRENT_TIMESTAMP
    DBTIMEZONE
    ERROR_ON_OVERLAP_TIME
    IDLE_TIME
    LOCALTIME
    LOCALTIMESTAMP
    PASSWORD_GRACE_TIME
    PASSWORD_LIFE_TIME
    PASSWORD_LOCK_TIME
    PASSWORD_REUSE_TIME
    SESSIONTIMEZONE
    SYSTIMESTAMP
    TIMESTAMP
    TIME
    TIMEOUT
    TIMEZONE_ABBR
    TIMEZONE_HOUR
    TIMEZONE_MINUTE
    TIMEZONE_REGION
    TIME_ZONE

    22 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    you can't use a column alias in a group by.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Apr 2003
    Posts
    20
    That is not my question. The questions is can I group based on the name I have given in the query:

    select myvalue as T from values group by T;

    Can I use 'T' in the group by expression and NOT myvalue (which is the column name).

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Just as shoblock said : you can't.

    Regards,

    RBARAER

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by islamabadi
    Select a.time as T from a group by T;

    ORA-00904: invalid column name

    What is wrong with this? Can I not use name 'T' in group by expression?
    (not counting the fact you have a column named "time")

    wouldn't that be the same as:
    select distinct a.time t from a;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Try renaming the column to something else.... like "thyme".

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    "wouldn't that be the same as: select distinct a.time t from a;"

    For some reason, there's always at least one developer at every site who insists that using a group by is faster than a distinct to produce the same results. Never tested it, never proved with elapsed times, just "knows" that it's true. Even worse is when this person convinces the new people that it is "always true"
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Apr 2003
    Posts
    20
    I guess I should have given the full query. I appreciat everybody's response but I wish it was that simple. Here is the query:

    select a.this, b.that, sum(c.another),
    case
    when a.me = 1 Then 'Hello'
    when a.me = 2 Then 'Here'
    end my_var
    from a, b, c
    where a.key = b.key
    and b.c_key = c.c_key
    group by a.this, b.that, my_var.

    Any I get ORA error for invalid name. How would I use the my_var in group function with the case statement?

    Any help would be appreciated.

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    you can't use a column alias in a group by. that's it. therefore, the only possible solution is... use the original column/psuedo-column/function (whatever) in the group by:

    select ..., case
    when a.me = 1 Then 'Hello'
    when a.me = 2 Then 'Here'
    end my_var
    from a, b, c
    ...
    group by ..., case
    when a.me = 1 Then 'Hello'
    when a.me = 2 Then 'Here'
    end
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    two other ways to do it:
    PHP Code:
    select 
      a
    .this
      
    b.that,
      (
    select sum(c.anotherfrom c where b.c_key c.c_keysum_of_c,
      
    decode(a.me1'Hello'2'There'a.memy_var
    from a
    b
    where a
    .key b.key
    PHP Code:
    select thisthatsum_of_c
      (case
        
    when a.me 1 Then 'Hello'
        
    when a.me 2 Then 'Here'
       
    endmy_var
    from
    (select a.thisb.thatsum(c.anothersum_of_ca.me
    from a
    bc where a.key b.key
      
    and b.c_key c.c_key
    group by a
    .thisb.that); 
    I prefer the DECODE over CASE in this situation, but do what ya like.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shoblock
    For some reason, there's always at least one developer at every site who insists that using a group by is faster than a distinct to produce the same results. Never tested it, never proved with elapsed times, just "knows" that it's true. Even worse is when this person convinces the new people that it is "always true"
    Yes, I've come across that nonsense too! My response is: if it were true that GROUP BY out-performs DISTINCT somehow, then why wouldn't Oracle optimize DISTINCT statements by turning them into GROUP BY statements, instead of doing them a different and inferior way?

    It's like all the blather one sees about count(1) v. count(null) v. count(*). It may have made a difference in Oracle 4.0 or whatever, but Oracle knocked it on the head years ago.

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As a matter of fact, I was reading an article that when oracle 8 came out, the optimizer started to automatically rewrite all count(1) to count(*) anyway.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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