I am retrieving the data from Oracle using JDBC API. I want to get the last 24 months starting from current month and the respective count of records.
I wrote the query as shown below:
(TIMESTAMP is of Data type DATE)
However, i cannot order by MONTH_YEAR as that will be ordered by alphabets(A,B,C..) as MONTH_YEAR is a string.
What i would appreciate is if anyone could point me how to order the result by date either on the DB query or on the Java end.
I tried putting your SQL inside code blocks, but it still was hard for me to read. This is possibly just a question of style (and I have yet to find a good style for SQL that everyone agrees to). I think your code can be rewritten as:
My first question is: why do you need the sub-clause?
Within your sub-clause, you have access to the timestamp, which I assume is a timestamp. If so, you could order it according to the timestamp itself.
I don't have access to an Oracle database right now, but I would have thought that this could be simplified:
The to_char function converts the date field according to your format specification. So specifying that the conversion should use a dash, then later converting the dash into an underscore seems redundant - couldn't you just specify the underscore in the to_char format definition?
use EXCTRACT conversion function, it extracts months and years as numbers,
it is available starting from Oracle 9i:
Also avoid using TIMESTAMP as a column name, TIMESTAMP is a data type,
it could cause confusion and Oracle can give you strange syntax errors in some cases.
But if you must, enclose it in brackets - like this- "TIMESTAMP".
Joined: Mar 12, 2007
@Andrew: I would imagine i cannot order by timestamp though i can by MONTH_YEAR which would give me the
You are right, No need of replace function
@Ireneusz: Point noted. This does work. Only thing is that for past 24 months, we need to subtract 23 months from sysdate.
WHERE "TIMESTAMP" >= TRUNC((ADD_MONTHS(SYSDATE, -23)), 'MM')