Using Numeric Indexes

[mcoupal]

I tried searching with no luck.

I would like to sort a table based on 3 numeric fields with values like:

Col1 | Col2 | Col3
-------------------
.75 | 103 | 299.5

To create an index on these three fields so that they are sorted ascending, do I have to convert them all to strings and concatenate? It seems like there should be a more elegant solution. Is there?

E.g., STR(thk,6,4) + STR(length,6,2) + STR(width,6,2)

-- Thanks Mike

 

[Mike Gagnon]

Is there a reason you cannot index them as numeric values?

CREATE CURSOR mycursor (val1 n(10,2),val2 n(10,2),val3 n(10,2))
INSERT INTO mycursor (val1,val2,val3) VALUES (.75,103,299.5)
INDEX ON val1+val2+val3 TAG val

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[mcoupal]

Hi Mike,

I didn't make it clear, but I'd like to sort by the first column, then second, then third. Summing them wouldn't necessarily sort them due to their variance.

.25, 100, 300
.50, 11, 80

If going by their sum, the .50 record would be sorted above .25 .. which isn't what I'd want.

Thanks,
Mike

 

[Mike Gagnon]

Then create a cursor from the table and use the order clause.

select * from myTable order by 1,2,3 into cursor myCursor

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRudisill]

I would use allt(str(thk,6,4))+etc... Padl() might be another option as well. If you are not wanting to create an index but just view the data in that order I would go with Mike's suggestion.

 

[Stella740pl]

Depending on your needs, you can go with Mike's SELECT suggestion, but you can also sum them using appropriate coefficients. Something like this should do:

thk*10^8 + length*10^3 + width

Then
.25, 100, 300 -> 25100300
.50, 11, 80 -> 50011080

and they will sort out correctly.
Of course, you should know the ranges of your data in advance to come up with correct coefficients.

 

[baltman]

This isn't too bad. Need to make sure data is left padded...

CREATE CURSOR mycursor (val1 n(10,2),val2 n(10,2),val3 n(10,2))
INSERT INTO mycursor VALUES (1.75,173,229.5)
INSERT INTO mycursor VALUES (.75,233,599.5)
INSERT INTO mycursor VALUES (3.75,563,2159.5)
INSERT INTO mycursor VALUES (.75,1333,599.5)
INSERT INTO mycursor VALUES (.75,233,299.5)

INDEX ON ;
PADL(TRANSFORM(val1),10," ")+;
PADL(TRANSFORM(val2),10," ")+;
PADL(TRANSFORM(val3),10," ") TAG SortTag

Brian

 

[mcoupal]

Thanks everyone who replied. The last 3 responses are exactly what I need. I couldn't just use a cursor because I need the user to modify this data and wanted to keep the records in order and not read-only.

I've gotten burnt in the past by trying to auto-sort tables rather than simply using indexes. The user was upset when, inexplicably, some of their data got lost-- lesson learned :-o

Thanks again
_Mike