Re: Want to sort CHAR field NUMERICALLY in ACE report
* Eric Thompson <thompson@netcom.com> wrote:
* >I have a situation in which I'd like to select a character field
* >from a table and sort it as though it were an integer. I know in advance
* >that all rows returned will have numeric values, even though the field
* >must remain a char field.
* >
* >I thought something like this should work, but it doesn't:
* >
* > select int(char_field)
* > from table
* > order by 1
* >
* >One inelegant method of solving this that I think might work is:
* >
* > select char_field
* > from table
* > order by char_field[1], char_field[2], ..., char_field[8]
* >
* >But that stops working after the eighth digit.
Can you do something like the following:
create temp table tmp_int_table ( tmp_num integer, rownum integer );
insert into tmp_int_table select char_field, rowid from table;
create index tmp1 on tmp_int_table ( tmp_num ); select table.*, tmp_int_table.*
from table, tmp_int_table
where table.rowid = tmp_int_table.rownum
order by tmp_int_table.tmp_num;
That way, it transforms the char var. to an integer. Just ignore the
two vars from the last select statement because they won't be needed
for the report.
Robert Minter |Data Systems Support| \\\\\\_///
Programmer, Software Development | Orange, CA | ( _ _ )
internet: rob@dssmktg.com | Tel: 714.771.0454 | (| ^ |)
bangpath: uunet.uu.net!dssmktg!rob| Fax: 714.771.3028 | \\`-'/
#include <disclaimer.h> SURF'S UP \\_/