Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Ordering the retrieved dates from Database

 
Piyush Mattoo
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks in advance!
 
Andrew Monkhouse
author and jackaroo
Marshal Commander
Pie
Posts: 11854
185
C++ Firefox Browser IntelliJ IDE Java Mac Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Andrew Monkhouse
author and jackaroo
Marshal Commander
Pie
Posts: 11854
185
C++ Firefox Browser IntelliJ IDE Java Mac Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

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".
 
Piyush Mattoo
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Andrew: I would imagine i cannot order by timestamp though i can by MONTH_YEAR which would give me the
alphabet ordering.

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')

Thankyou for your valuable opinions.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic