5 Replies Latest reply: Oct 25, 2012 12:22 PM by Sven W. RSS

    Dense_Rank() over order by date fields not working properly

    970700
      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?
        • 1. Re: Dense_Rank() over order by date fields not working properly
          Frank Kulash
          Hi,
          user12270778 wrote:
          ... Can anyone please help me on how to solve this?
          Sorry, probably not, unless you post some sample data (CREATE TABLE and INSERT statements) and the results you want from that sample data.
          See the forum FAQ {message:id=9360002}

          The string literal 'COMPLIANCETICKSDATE' will never be equal to 'TASKNBR', 'DESCRIPTION', 'PARTNBR', or any of the other literals in the DECODE statement. As a result, the DECODE expression uyou posted is always returning compliance_date. Perhaps you meant
          DECODE ( UPPER (complianceticksdate), ...
          without the single-quotes.

          What is the data type of min_due_date?
          If it's a DATE, there's no need to call TO_DATE to convert it to a DATE.
          If it's not a DATE, you have a very bad table design problem.

          Why are you computing r_num? Typically, values like r_num are compute so you can use them in the main query, for example:
          WHERE     r_num  = 1
          Do you really only want to display it?
          • 2. Re: Dense_Rank() over order by date fields not working properly
            Stew Ashton
            The ORDER BY within an analytic function does not guarantee an ORDER in the result set.

            The only way to guarantee an order in the result set is to have an ORDER BY clause after the SELECT.

            We don't necessarily know why Oracle decided it was cheaper and faster to give you the rows that way. We do know that Oracle has the right to give you the rows any way it wants, unless you put that ORDER BY clause in there.
            • 3. Re: Dense_Rank() over order by date fields not working properly
              Sven W.
              user12270778 wrote:
              Hi,
              i have a pl/sql statement which looks like this:
              ...

              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.
              Can anyone please help me on how to solve this?
              The problem is an implicit date conversion that takes place.
              Because the first parameters of your decode are strings, the COMPLIANCE_DATE column is also converted into a string. This string is then sorted alphanumerically. The default format mask is often DD-MON-RR. Therefore your date column would be sorted by day, then by the name of the month and so on.

              Fast solution would be to do an explicit conversion from date into strings that match the ordering.
              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', to_char(COMPLIANCE_DATE,'YYYYMMDD HH24:MI:SS'), /* this is the important bit */
                                             TASK_NBR
                                             ) DESC NULLS LAST,task_nbr,description,enrtask_id)     AS r_num
                                  FROM     GT_STATUS_PAGING  gsp
                       )
              SELECT     *
              FROM     OrderedByDateTable     
              You should be careful, because the same problem also happens with your SORTDATE column MIN_EST_DUE_DATE.

              Edited by: Sven W. on Oct 25, 2012 6:53 PM
              • 4. Re: Dense_Rank() over order by date fields not working properly
                John Spencer
                Decode can only return a single datatype. That is, it cannot conditionally return a number under come conditions and a date under others. From the documentation:

                "Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2."

                So, assuming that MIN_EST_DUE_DATE and COMPLIANCE_DATE are ment to represent dates, then you would need to use an explicit format mask to get them sorting corrently. For the compliance_date, something like:
                to_char(compliance_date, 'yyyymmdd')
                I am very suspicious about the to_date on mis_est_due_date. If it really is a date column then do it the same way as compliance_date, if not then use an explicit format mask in the to_date to make it a date then to_char it to sort proprly.

                John
                • 5. Re: Dense_Rank() over order by date fields not working properly
                  Sven W.
                  Decode can only return a single datatype. That is, it cannot conditionally return a number under come conditions and a date under others. From the documentation:
                  John made a very good point here. This made me arware of another potential conversion problem that you might have. If some of the columns are a number datatype then it might happen that oracle converts them also to string. Which can also lead to problems.

                  For example the numbers
                    13
                   120
                  1401
                  would be sorted after they are converted to string like this
                  120
                  13
                  1401
                  Which is probably not want you want. This sorting difference is often hard to spot in real data, because usually the numbers have the same length and are in a similiar range. And if the output size is big, nobody looks at the very end where suddenly all the small 9er numbers are.

                  Edited by: Sven W. on Oct 25, 2012 7:21 PM