Oracle: TO_CHAR Solution

Working with a colleague this morning and the requirement, on the surface was simple: for a selected product, show the sales, in a window, for each month, going backwards in time. Initially we are going to create a loop going backwards in time, a month at a time, doing a SELECT against the database to retrieve the sales for each month.

Andrew Deacon, my colleague, came up with this superb solution which is astoundingly quick and efficient, letting the database do all the work. All we had to then was display the results set on the form, with appropriate column headings. Here is the solution we used:

select TO_CHAR(std_date, 'MONTH YYYY'), TO_CHAR(std_date, 'MM'), TO_CHAR(std_date, 'YYYY'), SUM(STD_TOTAL) from guser.f_strline where std_prcode = ‘PRODCODE’ GROUP BY TO_CHAR(std_date, 'MONTH YYYY'), TO_CHAR(std_date, 'MM'), TO_CHAR(std_date, 'YYYY') ORDER BY TO_CHAR(std_date, 'YYYY') DESC, TO_CHAR(std_date, 'MM') DESC

With a combination of Oracle’s TO_CHAR functionality and GROUP BY, the solution is slick. I like stuff like this ☺

“You can’t keep blaming yourself. Just blame yourself once, and move on.” - Homer Simpson