|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
|
|||
|
|||
|
Hello,
I've got a SQL statement that pulls data based on to_date('January 2000', 'Month yyyy') that returns results, but it is not calendar aware. The order by won't let me sort by 'calendar position', instead it just sorts alphabetically by the month. Here is my statement: SELECT to_char(a.row_add_tms, 'Month yyyy') as row_add_date, b.DCMNT_CLASS_TYPE_DESC,a.dcmnt_class_seq_nbr, sum(a.img_qty) img_qty FROM dcmnt_cmit_cnt a, dcmnt_class_type b, rpt_grp_type c WHERE a.DCMNT_CLASS_SEQ_NBR = b.DCMNT_CLASS_SEQ_NBR AND c.rpt_grp_typ_cd = '3' AND c.rpt_grp_typ_cd = b.rpt_grp_typ_cd AND a.row_add_tms >= to_date('January 2000', 'Month yyyy') AND a.row_add_tms <= to_date('December 2004', 'Month yyyy') GROUP BY a.DCMNT_CLASS_SEQ_NBR, to_char(a.row_add_tms, 'Month yyyy'), b.DCMNT_CLASS_TYPE_DESC ORDER BY to_char(row_add_tms, 'Month yyyy'), b.dcmnt_class_type_desc; Any suggestions? Thanks, -Rick |
|
#2
|
||||
|
||||
|
Your problem lies in
Quote:
you are not ordering by a date, but by a char, so it Quote:
Use the original field.
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins |
|
#3
|
|||
|
|||
|
That had occured to me yesterday after I posted. Thanks-
|
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Another to_date() question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|