10 Replies Latest reply: Sep 18, 2013 2:13 PM by bb840c44-d5bd-49ad-a77d-84ab023033bd RSS

    ORA-00904:  invalid identifier

    11g.DBA
      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
          did you run the both query under the same user ?
          • 2. Re: ORA-00904:  invalid identifier
            11g.DBA
            Yes, the very same user
            • 3. Re: ORA-00904:  invalid identifier
              asahide
              Hi,

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

              Regards,
              • 4. Re: ORA-00904:  invalid identifier
                11g.DBA
                Oracle 11.2.0.1.0
                database stored remotely & accessing it using putty
                • 5. Re: ORA-00904:  invalid identifier
                  Osama_Mustafa
                  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
                    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
                      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
                        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
                          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

                            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