How to order a varchar....
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?