Re: numeric sorting on a char field

Rob Wilson wrote: > > 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. IDS (no options) is not object relational until 9.2. Even then I think you have to pay for UDO to use OO. But you are on the right lines. Try: SELECT TRUNC(char_field + 0) FROM my_table ORDER BY 1 You don't even need the TRUNC if the report formats the number (USING) correctly. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |What year 2000 bug? year 2000 bug? |/// / ////| | Fax: +27 838250 2325 |year 2000 bug? year 2000 bug? year |// / /////| |Cell: +27 83 250 2325 |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+