numeric sorting on a char field
gurus,
i have a char(5) field, which contains nothing but numerics. my user doesnt
like the report output, which resembles this:
1
10
100
2
20
200
...
the applicable code resembles this:
define c_cursor cursor for
select character_field from table_name
order by character_fieldforeach c_cursor into character_field
output to report_name(character_field)
end foreach
short of pitching this code and sorting it prior to the 'output to' stmt, or
rewriting the 50 pgms which insert into this table to right-justify the
data, is there any slick way to sort a char field numerically? (in my dreams
i imagine a clause, "order by character_field numerically")
In article <375ed2b0@news1.one.net>, DSL <dsl@one.net> wrote:
>gurus,
>
>i have a char(5) field, which contains nothing but numerics. my user doesnt
>like the report output, which resembles this:
>1
>10
>100
>2
>20
>200
>...
>the applicable code resembles this:
>
>define c_cursor cursor for
> select character_field from table_name
> order by character_field>foreach c_cursor into character_field
> output to report_name(character_field)
>end foreach
>
>short of pitching this code and sorting it prior to the 'output to' stmt, or
>rewriting the 50 pgms which insert into this table to right-justify the
>data, is there any slick way to sort a char field numerically? (in my dreams
>i imagine a clause, "order by character_field numerically")
I guess you could do something like this:-
define c_cursor cursor for
select character_field from table_name ## no 'order by' necessary here
let integer_field = character_field
foreach c_cursor into character_field
output to report_name(character_field, integer_field)
end foreach
then in the report you can say "order by r_integer_field"
Obviously integer_field & r_integer_field are defined as integer -
it is basically just a dummy variable that the report will sort on.
See if that will do it for you?
- Paul
DSL (dsl@one.net) wrote: : gurus, [snip] : short of pitching this code and sorting it prior to the 'output to' stmt, or : rewriting the 50 pgms which insert into this table to right-justify the : data, is there any slick way to sort a char field numerically? (in my dreams : i imagine a clause, "order by character_field numerically") Try something like: SELECT character_field::integer FROM table ORDER BY 1; I know this works for 9.X, haven't tried 7.X though. Basically, the :: operator casts form one type to another. -- Rob Wilson rwilson@ntsource.com
Paul Roberts wrote:
>
> In article <375ed2b0@news1.one.net>, DSL <dsl@one.net> wrote:
> >gurus,
> >
> >i have a char(5) field, which contains nothing but numerics. my user doesnt
> >like the report output, which resembles this:
> >1
> >10
> >100
> >2
> >20
> >200
> >...
> >the applicable code resembles this:
> >
> >define c_cursor cursor for
> > select character_field from table_name
> > order by character_field> >foreach c_cursor into character_field
> > output to report_name(character_field)
> >end foreach
> >
> >short of pitching this code and sorting it prior to the 'output to' stmt, or
> >rewriting the 50 pgms which insert into this table to right-justify the
> >data, is there any slick way to sort a char field numerically? (in my dreams
> >i imagine a clause, "order by character_field numerically")
>
> I guess you could do something like this:-
>
> define c_cursor cursor for
> select character_field from table_name ## no 'order by' necessary here>
> let integer_field = character_field
>
> foreach c_cursor into character_field
> output to report_name(character_field, integer_field)
> end foreach
>
> then in the report you can say "order by r_integer_field"
>
> Obviously integer_field & r_integer_field are defined as integer -
> it is basically just a dummy variable that the report will sort on.
> See if that will do it for you?
That will work just fine, Paul, he could also do:
define c_cursor cursor for
select (char_field + 0)
from table_name
order by 1;
and then order by...external in the report module.
Art S. Kagel