This discussion is archived
10 Replies Latest reply: Sep 18, 2013 12:13 PM by bb840c44-d5bd-49ad-a77d-84ab023033bd RSS

ORA-00904:  invalid identifier

11g.DBA Newbie
Currently Being Moderated
Hi all,

Am getting error invalid identifier when i run the following command.Please help where i am going wrong

SELECT 'DESS' as DTYPE,
     count(*) as TOTALVAL,
     mas_diet_combination.diet_combination_name,
     mas_diet_combination.diet_combination_id as dietCombinationId,
     diet_details.diet_date as dietdate
     FROM diet_details,mas_diet_combination
     left join mas_diet_combination on diet_details.diet_combination_id=mas_diet_combination.diet_combination_id
     where diet_details.diet_date between '03-DEC-08' and '04-DEC-08'
group by diet_details.diet_date,mas_diet_combination.diet_combination_id,mas_diet_combination.diet_combination_name

ERROR at line 7:
ORA-00904: "DIET_DETAILS"."DIET_COMBINATION_ID": invalid identifier


Select diet_combination_id from diet_details;

DIET_COMBINATION_ID
-------------------
22
22
22
22
22
22
  • 1. Re: ORA-00904:  invalid identifier
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    did you run the both query under the same user ?
  • 2. Re: ORA-00904:  invalid identifier
    11g.DBA Newbie
    Currently Being Moderated
    Yes, the very same user
  • 3. Re: ORA-00904:  invalid identifier
    asahide Expert
    Currently Being Moderated
    Hi,

    What's your oracle version ?
    And Are these tables local table (not use dblink)?

    Regards,
  • 4. Re: ORA-00904:  invalid identifier
    11g.DBA Newbie
    Currently Being Moderated
    Oracle 11.2.0.1.0
    database stored remotely & accessing it using putty
  • 5. Re: ORA-00904:  invalid identifier
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Try this please
    SELECT 'DESS' as DTYPE,
    count(*) as TOTALVAL,
    mas_diet_combination.diet_combination_name,
    mas_diet_combination.diet_combination_id as dietCombinationId,
    DIET_DETAILS.DIET_COMBINATION_ID,
    diet_details.diet_date as dietdate
    FROM diet_details,mas_diet_combination
    left join mas_diet_combination on diet_details.diet_combination_id=mas_diet_combination.diet_combination_id
    where diet_details.diet_date between '03-DEC-08' and '04-DEC-08'
    group by diet_details.diet_date,mas_diet_combination.diet_combination_id,mas_diet_combination.diet_combination_name
  • 6. Re: ORA-00904:  invalid identifier
    11g.DBA Newbie
    Currently Being Moderated
    Still the same error

    SQL> SELECT 'DESS' as DTYPE,
    count(*) as TOTALVAL,
    mas_diet_combination.diet_combination_name,
    2 3 4 mas_diet_combination.diet_combination_id as dietCombinationId,
    5 DIET_DETAILS.DIET_COMBINATION_ID,
    6 diet_details.diet_date as dietdate
    7 FROM diet_details,mas_diet_combination
    8 left join mas_diet_combination on diet_details.diet_combination_id=mas_diet_combination.diet_combination_id
    9 where diet_details.diet_date between '03-DEC-08' and '04-DEC-08'
    10 group by diet_details.diet_date,mas_diet_combination.diet_combination_id,mas_diet_combination.diet_combination_name;
    left join mas_diet_combination on diet_details.diet_combination_id=mas_diet_combination.diet_combination_id
    *
    ERROR at line 8:
    ORA-00904: "DIET_DETAILS"."DIET_COMBINATION_ID": invalid identifier
  • 7. Re: ORA-00904:  invalid identifier
    jaasteij Newbie
    Currently Being Moderated
    You are performing both a cross join on mas_diet_combination ( FROM diet_details,mas_diet_combination) and a left join on mas_diet_combination without using an alias. So your join-condition is not clear. Use a table alias or re-consider your join-condition. Most probably the cross join is incorrect.
  • 8. Re: ORA-00904:  invalid identifier
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    i notice that you are alias to your columns but you are still use the same name for the column !!! why Use alias or the Column name
  • 9. Re: ORA-00904:  invalid identifier
    EdStevens Guru
    Currently Being Moderated
    11g.DBA wrote:
    Oracle 11.2.0.1.0
    database stored remotely & accessing it using putty
    No, you are NOT accessing the database using putty. You are establishing an ssh connection to the database server using putty. Whatever you do in putty (including sqlplus or some other process) is local to that server.

    When asked "And Are these tables local table (not use dblink)?" it is in reference to the relationship of the queried objects to the database to which you are connected, not the relationship between them and your workstation.
  • 10. Re: ORA-00904:  invalid identifier
    bb840c44-d5bd-49ad-a77d-84ab023033bd Newbie
    Currently Being Moderated

    Try this:SELECT 'DESS' as DTYPE,

         count(*) as TOTALVAL,

         mas_diet_combination.diet_combination_name,

         mas_diet_combination.diet_combination_id as dietCombinationId,

         diet_details.diet_date as dietdate

         FROM diet_details

         left join mas_diet_combination on diet_details.diet_combination_id=mas_diet_combination.diet_combination_id

         where diet_details.diet_date between '03-DEC-08' and '04-DEC-08'

    group by diet_details.diet_date,mas_diet_combination.diet_combination_id,mas_diet_combination.diet_combination_name

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points