CODEWITHSUNDEEP
sqloracle-database
Loppas
Loppas

Reputation: 1

MONTH invalid identifier in Oracle

All ingredients purchased in November. Display the ingredient id, name, date ordered, quantity & unit (in one column), unit price, and total amount for each item. Order the result set by descending date ordered. Hint: total amount is quantity multiplied by unit price.

SELECT ingredient_id, ingredient_name, date_ordered, "Quantity & Unit" AS Quantity,
unit_price * quantity AS "Total Amount"
FROM ingredient
WHERE MONTH (date_ordered) = 11
ORDER BY 'date_ordered' DESC;

It keeps saying MONTH invalid identifier.

Upvotes: 0

Views: 8375

Answers (2)

Popeye
Popeye

Reputation: 35900

There is multiple issues in your code:

  • MONTH is not a valid function in oracle
  • 'date_ordered' is invalid as single quotes are not allowed for column names.

Your code should look like following:

SELECT ingredient_id, ingredient_name, date_ordered, "Quantity & Unit" AS Quantity,
unit_price * quantity AS "Total Amount"
FROM ingredient
WHERE extract (MONTH from date_ordered) = 11 -- used extract function
ORDER BY date_ordered DESC; -- removed single quotes

Also note that this query will give all the records of november month regardless of the year.

Cheers!!

Upvotes: 4

Ed Bangga
Ed Bangga

Reputation: 13006

use extract() function

SELECT ingredient_id, ingredient_name, date_ordered, "Quantity & Unit" AS Quantity, unit_price * quantity AS "Total Amount" FROM ingredient WHERE extract(month from date_ordered) = 11 ORDER BY 'date_ordered' DESC;

Another option also is to use.

To_Char(date_ordered,'MM')

Upvotes: 1

Related Questions