Re: Sorting integer field
->Date: Wed, 17 Feb 93 15:25:07 EST
->From: RUCHI@CFRVM.CFR.USF.EDU
->To: informix-list@rmy.emory.edu
->Subject: Sorting integer field
->Sender: informix-list-owner@rmy.emory.edu
->
->I have an integer field that I like to sort using the LAST 2 NUMBERS on
->integer field. For example:
->
->Before Sorting the values looks like the following:
->1
->2
->9
->107
->202
->204
->10
->
->After Sorting the values should look like the following:
->1
->2
->202
->204
->107
->9
->10
->
->Any idea? solution? Please help
->
->Thanks.
->
->Ruchi.
->
Assuming you are working in 4GL:
1. Create a TEMP table with a CHAR field of adequate width (8 in this
example) in place of the original table.
2. Using a cursor, select the rows you want from the original table.
3. For each row, do something like:
LET char_var = int_var USING "########"
4. Store row in TEMP table, with char_var replacing int_var.
5. Then
SELECT * FROM temp_table ORDER BY char_var[7,8]
I know that the above works, since I have done similar things. I am not
aware of any way to do this in straight ISQL. ISQL does not provide an
int() or trunc() function. Unfortunately,
SELECT (intvar - intvar/100*100) FROM tbl ORDER BY 1
does not work, since Informix evaluates the expression in floating point,
and it always evaluates to 0.00. IF we could do
SELECT (intvar - TRUNC(intvar/100)*100) FROM tbl ORDER BY 1
then it would work.
Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, LSC | ( Please note: My opinions do not ) |
| P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179 | Voice: 303-977-9998 |
+------------------------------+---------------------------------------+