Order by month name
939659Jun 20 2012 — edited Jun 21 2012Hi all!
I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:
SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 5 THEN 'MAY' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 6 THEN 'JUN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 7 THEN 'JUL' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 8 THEN 'AUG' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 9 THEN 'SEP' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 10 THEN 'OCT' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 11 THEN 'NOV' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 12 THEN 'DEC' || ' ' || EXTRACT(YEAR FROM transaction_date)
END AS month
FROM transaction
WHERE EXTRACT(YEAR FROM transaction_date) = 2009
ORDER BY TO_DATE(month, 'MM YYYY');
The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause
ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));
But I got an ORA-01866: the datetime class is invalid. I'm really not sure what's going on. I'm using Oracle 10g xe (outdated, I know, but it's what my professor suggested we use). Does anybody have any suggestions that would help get the months to show up in order? Many thanks!