Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dense_Rank() over order by date fields not working properly

970700Oct 25 2012 — edited Oct 25 2012
Hi,
i have a pl/sql statement which looks like this:
WITH OrderedByDateTable AS
(

SELECT gsp.*, DENSE_RANK () OVER (ORDER BY A_varChar_col1,
decode(upper('COMPLIANCETICKSDATE'),'TASKNBR',TASK_NBR,
'DESCRIPTION', DESCRIPTION,
'PARTNBR', PART_NBR,
'PARTSERIALNBR', PART_SERIAL_NBR,
'SORTDATE', TO_DATE(MIN_EST_DUE_DATE),
'COMPLIANCETICKSDATE', COMPLIANCE_DATE,
TASK_NBR
)DESC NULLS LAST,task_nbr,description,enrtask_id) AS r_num
FROM GT_STATUS_PAGING gsp
)
SELECT *
FROM OrderedByDateTable


The result that i am expecting is that the dense_rank should order the compliance_date column in desc order and finally gives ranks to the rows.
But i am getting the resultset in some order all the time, but it is neither ASC or DESC.

But if for debugging purposes, lets say if i replace the COMPLIANCE_DATE column with DESCRIPTION column again it works perfectly fine giving me the descriptions in DESC order along with the ranks. But for both the date fields above it doesnt work.

Can anyone please help me on how to solve this?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 22 2012
Added on Oct 25 2012
5 comments
1,836 views