>>I have table with 8 numberic fields that contain numbers 0-2 . I am trying to find out how many uniq rows (combinations) that I have. I use this index statement but it don't work.
>>
>>inde on field1 + field2 + field3 + field4 +field5 + field6 + field7 + field8 to uniq c:\temp\inde
>>
>>Anyone else have any ideas?
>
>Since they're numeric, you are summing up the field values instead of concatenating them.
>You probably don't really need an index on this field combination, so why not use a select instead?
>
SELECT field1, field2, field3, field4, field5, field6, field7, field8, COUNT(*) AS unique_cnt;
> FROM mytable ;
> GROUP BY field1, field2, field3, field4, field5, field6, field7, field8 ;
> INTO CURSOR cutmp
>
>If you do need the index
>
INDEX ON TRANSFORM(field1) + TRANSFORM(field2).... UNIQUE TAG mycnt
This index would work in assumption, what all these fields have Numeric 1.
If it's not true, instead of transform, I'd use:
index on str(field1,fsize(field1))+...
Use str and put real length of your numeric fields here.
Use can also use padl function, IOW:
pald(field1,fsize(field1),'0')+...
Say, you have field1 Numeric 5, field2 Numeric 3
your index would be index on padl(field1,5,'0')+padl(field2,3,'0')
Data:
1 0
0 2
1 2
you will have:
'00000002'
'00001000'
'00001002'
If it's not broken, fix it until it is.
My Blog