Month listings - A more elegant solution 1

[Adammon]

I've taken over an XSQL/XML application and I've ran into an odd little work-around by a former employee that I'd like to reduce to a more simple solution. I need a list of months, in order from Current Month all the way back to, say, January 2000. The old solution was:

SELECT
TO_CHAR(SYSDATE), 'fmMonth yyyy') month,
TO_CHAR(ADD_MONTHS(SYSDATE), -1), 'fmMonth yyyy') month,
TO_CHAR(ADD_MONTHS(SYSDATE), -2), 'fmMonth yyyy') month,
TO_CHAR(ADD_MONTHS(SYSDATE), -3), 'fmMonth yyyy') month
etc etc etc.

This worked fine, I don't necessarily need them all under the same field, I just need a list of month. I don't want to have to repeat this code all the way to
TO_CHAR(ADD_MONTHS(SYSDATE), -50), 'fmMonth yyyy') month

Is there a more elegant solution? I suppose I could create a MONTH table with months listed all the way to January 2000, but, of course, I'm just a developer, not a DBA, and creating tables falls outside my 'realm of responsibility. I can keep the existing code, I'm just hoping that, if someone else has to take over this code, they don't laugh at me for not changing it.

Thanks

 

[carp]

If you have a known number of months you would like to track back through, you might try something like:

SELECT TO_CHAR(ADD_MONTHS(sysdate,1 - rownum),'fmMonth yyyy')
FROM dba_objects
WHERE rownum <= 10;

TO_CHAR(ADD_MO
--------------
February 2004
January 2004
December 2003
November 2003
October 2003
September 2003
August 2003
July 2003
June 2003
May 2003

10 rows selected.

BTW, this is not ANSI SQL, so it's not a good answer in this forum. However, it looks like you're using Oracle so it should work for you.

 

[Adammon]

Thanks for the input Carp. I had thought of that solution myself. The problem was that I'd have to change the number of rows to return each month. I need something static that goes back to January 2000 and I'm having a doozy of a time thinking of it.

 

[PHV]

Can't you in Oracle count the number of months between January 2000 and today ?

Hope This Help
PH.

 

[carp]

OK, if your earliest month isn't going to change, then how about:

SELECT TO_CHAR(ADD_MONTHS(sysdate,1 - rownum),'fmMonth yyyy')
FROM dba_objects
WHERE ADD_MONTHS(sysdate,1 - rownum) >= to_date('1-JAN-2000','DD-MON-YYYY');

Elbert, CO
1627 MST