im stuck and i need my lovely SFH friends hehe, (hopefully demon will see this he seems to know this kinda stuff (maybe gam gam's).

i have this sql query:

SELECT     ROWNUM - 61 AS MONTH, TO_CHAR(ADD_MONTHS(SYSDATE, 1 - 1 * ROWNUM + 60), 'MON YY') AS MONTH_LABEL
FROM         (SELECT     SYSDATE AS DATETODAY
                       FROM          DUAL
                       GROUP BY CUBE(1, 2, 3, 4, 5, 6, 7, 8)) DERIVEDTBL
WHERE     (ROWNUM < 120)

it basically creates a list of dates and index numbers:

 -60     SEP 11
 -59     AUG 11
 -58     JUL 11
 -57     JUN 11
 -56     MAY 11
 -55     APR 11
 -54     MAR 11
 -53     FEB 11
 -52     JAN 11
 -51     DEC 10
 -50     NOV 10

(this one does negative through to positive)

So the problem is CUBE is only supported by oracle 8.1 and my database doesnt use it, any anyone else think of a way to get this sequence of numbers without using cube.

cheers

i think ur using the command in the wrong syntax

http://databases.about.com/od/sql/l/aacuberollup.htm

Funny you should ask about SQL, ive been doing lots of it this week, but my queries are much simpler, these ones filter a table of installed applications (the list you get in the Add/Remove Programs window), for windows hotfixes:

use eXpress;
SELECT computer.name as 'Server',application.name as 'Hotfix'
FROM application, computer
WHERE application.computer_id = computer.computer_id
AND
((application.name LIKE 'KB%') OR
(application.name LIKE 'Q1%') OR
(application.name LIKE 'Q2%') OR
(application.name LIKE 'Q3%') OR
(application.name LIKE 'Q4%') OR
(application.name LIKE 'Q5%') OR
(application.name LIKE 'Q6%') OR
(application.name LIKE 'Q7%') OR
(application.name LIKE 'Q8%') OR
(application.name LIKE 'Q9%') OR
(application.name LIKE 'Q0%'))
ORDER BY computer.name;

use eXpress;
SELECT computer.name as 'Server',COUNT(DISTINCT application.name) as 'Total Hotfixes' FROM application,computer
WHERE application.computer_id = computer.computer_id
AND
((application.name LIKE 'KB%') OR
(application.name LIKE 'Q1%') OR
(application.name LIKE 'Q2%') OR
(application.name LIKE 'Q3%') OR
(application.name LIKE 'Q4%') OR
(application.name LIKE 'Q5%') OR
(application.name LIKE 'Q6%') OR
(application.name LIKE 'Q7%') OR
(application.name LIKE 'Q8%') OR
(application.name LIKE 'Q9%') OR
(application.name LIKE 'Q0%'))
GROUP BY computer.name;

its using a strange syntax but it just creates a large number of rows. it does work in a table we have on 8.1 but not on the 8.0.

i just found

SELECT     ROWNUM - 61 AS MONTH
FROM         USER_OBJECTS
WHERE     (ROWNUM < 60)

but its limited to the number of records in user_objects which is a shame.

still looking....

a list of numbers

+ative ones with a month field being ones before this month

0 = this month

-ative ones with a month field being ones after this month

so

-2 | nov 06

-1 | oct 06

0  | sept 06

1 | Aug 06

2 | jul 06

but i need to do it upto 60 each way

cheers gould but still no cigar lol..

i want to be able to create a list of numbers -60 to + 60 then in another colum i want from 0 being this month a list of months increasing as the numbers go minus and decreasing the month as the numbers increase