This discussion is archived
2 Replies Latest reply: Oct 2, 2012 8:04 AM by 948141 RSS

How to order a varchar....

948141 Newbie
Currently Being Moderated
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....
    AmithY Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points