2 Replies Latest reply: Oct 2, 2012 10:04 AM by 948141 RSS

    How to order a varchar....

    948141
      hi to everyone...

      i'm working on oracle bi 10.

      i have a table (pivot)... and on the top, in the "page", i have a field called "DECADE".

      this one, is a varchar (result of the concat of 2 dates)

      it shows:

      DECADE

      01/10/2011 - 10/10/2011
      01/11/2011 - 10/11/2011
      01/12/2011 - 10/12/2011
      05/09/2011 - 10/09/2011
      11/09/2011 - 20/09/2011
      11/10/2011 - 20/10/2011
      11/11/2011 - 20/11/2011
      11/12/2011 - 20/12/2011
      21/09/2011 - 30/09/2011
      21/10/2011 - 31/10/2011
      21/11/2011 - 30/11/2011
      21/12/2011 - 31/12/2011

      as u can see, they are NOT ordered!!!


      if i add the data value, it will change in:

      DATA || DECADE

      05/09/2011 || 05/09/2011 - 10/09/2011
      06/09/2011 || 05/09/2011 - 10/09/2011
      07/09/2011 || 05/09/2011 - 10/09/2011
      08/09/2011 || 05/09/2011 - 10/09/2011
      09/09/2011 || 05/09/2011 - 10/09/2011
      10/09/2011 || 05/09/2011 - 10/09/2011
      11/09/2011 || 11/09/2011 - 20/09/2011
      12/09/2011 || 11/09/2011 - 20/09/2011
      13/09/2011 || 11/09/2011 - 20/09/2011
      14/09/2011 || 11/09/2011 - 20/09/2011
      15/09/2011 || 11/09/2011 - 20/09/2011
      16/09/2011 || 11/09/2011 - 20/09/2011

      as u can see, DECADE is repeated as many as the number of dates( and it's right 'cause from 05/09 until 10/09, this range is into DECADE 05/09/2011 - 10/09/2011).

      in this way, decade is ordered (what i really want) but is repeaded.


      i would to see:

      05/09/2011 - 10/09/2011
      11/09/2011 - 20/09/2011
      21/09/2011 - 30/09/2011
      01/10/2011 - 10/10/2011
      11/10/2011 - 20/10/2011
      21/10/2011 - 31/10/2011

      and so on...


      how can i order the fild DECADE without data field?
        • 1. Re: How to order a varchar....
          Amith Y
          Barticchia wrote:
          hi to everyone...

          i'm working on oracle bi 10.

          i have a table (pivot)... and on the top, in the "page", i have a field called "DECADE".

          this one, is a varchar (result of the concat of 2 dates)

          it shows:

          DECADE

          01/10/2011 - 10/10/2011
          01/11/2011 - 10/11/2011
          01/12/2011 - 10/12/2011
          05/09/2011 - 10/09/2011
          11/09/2011 - 20/09/2011
          11/10/2011 - 20/10/2011
          11/11/2011 - 20/11/2011
          11/12/2011 - 20/12/2011
          21/09/2011 - 30/09/2011
          21/10/2011 - 31/10/2011
          21/11/2011 - 30/11/2011
          21/12/2011 - 31/12/2011

          as u can see, they are NOT ordered!!!


          if i add the data value, it will change in:

          DATA || DECADE

          05/09/2011 || 05/09/2011 - 10/09/2011
          06/09/2011 || 05/09/2011 - 10/09/2011
          07/09/2011 || 05/09/2011 - 10/09/2011
          08/09/2011 || 05/09/2011 - 10/09/2011
          09/09/2011 || 05/09/2011 - 10/09/2011
          10/09/2011 || 05/09/2011 - 10/09/2011
          11/09/2011 || 11/09/2011 - 20/09/2011
          12/09/2011 || 11/09/2011 - 20/09/2011
          13/09/2011 || 11/09/2011 - 20/09/2011
          14/09/2011 || 11/09/2011 - 20/09/2011
          15/09/2011 || 11/09/2011 - 20/09/2011
          16/09/2011 || 11/09/2011 - 20/09/2011

          as u can see, DECADE is repeated as many as the number of dates( and it's right 'cause from 05/09 until 10/09, this range is into DECADE 05/09/2011 - 10/09/2011).

          in this way, decade is ordered (what i really want) but is repeaded.


          i would to see:

          05/09/2011 - 10/09/2011
          11/09/2011 - 20/09/2011
          21/09/2011 - 30/09/2011
          01/10/2011 - 10/10/2011
          11/10/2011 - 20/10/2011
          21/10/2011 - 31/10/2011

          and so on...


          how can i order the fild DECADE without data field?
          I am not sure if you are using OBIEE 10g? but the way you implement this kind of requirement is by adding a date field that is used in the concatenation of th DECADE column, and sort and hide it. Based on the sort order of the hidden column, the DECADE column will automatically get sorted.
          • 2. Re: How to order a varchar....
            948141
            exactly...that what i've done!!! i've added and hidden the data's column... in that way i've sorted but the problem is... DECADE is repeated!!!

            i'd like to see a distinct(DECADE) ordered