1 Reply Latest reply: Feb 8, 2013 1:17 AM by 11g.DBA RSS

    ORA-00904: "YEAR": invalid identifier

    11g.DBA
      Hi,

      I am getting ORA-00904: "YEAR": invalid identifier error while running the following query.
      This query was written in MySQL and being migrated to Oracle.
      The problem is in where clause as i am unable to migrate the YEAR,MONTHNAME

      "diet_details.diet_date = 01-JUL-12"

      SELECT
      diet_details.diet_id AS diet_id,
      diet_details.diet_combination_id AS diet_combination_id,
      count(diet_details.diet_details_id)AS count,
      mas_diet_combination.diet_combination_name AS mas_diet_combination_name,
      diet_details.diet_date AS diet_diet_date,
      diet_details.diet_details_id AS diet_diet_details_id,
      mas_department.department_name AS mas_department_name,
      mas_hospital.hospital_name AS mas_hospital_name
      FROM
      mas_diet_combination RIGHT OUTER JOIN diet_details ON mas_diet_combination.diet_combination_id = diet_details.diet_combination_id
      LEFT OUTER JOIN mas_department ON diet_details.department_id = mas_department.department_id
      LEFT OUTER JOIN mas_hospital ON diet_details.hospital_id = mas_hospital.hospital_id

      WHERE
      monthname(diet_details.diet_date) =JUL
      and year(diet_details.diet_date) = 12
      and diet_details.department_id = 11


      GROUP BY
      diet_details.diet_id,
      diet_details.diet_combination_id,
      diet_details.diet_details_id,
      mas_diet_combination.diet_combination_name,
      diet_details.diet_date,
      diet_details.diet_details_id,
      mas_department.department_name,
      mas_hospital.hospital_name
      order by mas_diet_combination.diet_combination_id