Composite indexing

[ameedoo3000]

hi all
i have simple table contain three fields ( name - gender - class )
i want to create index to order the three fields like this index on class + gender descending + name tag Ahmed
there are syntax error !
i want ascending for filed and descending for another field in the same index
how can i do this

 

[Mike Lewis]

You can't put DESCENDING in the middle of the expression.

If you want Gender to be descending and the other two fields to be ascending, you need a workaround. How you so that depends on how you are storing your Gender field.

If Gender can contain two values, M adn F, and you want the Ms to appear above the Fs, you could do something like this:

[tt]INDEX ON Class + IIF(Gender = "M", "0", "1") + Name TAG Ahmend
[/tt]

If you are storing the Gender in some other format, you will need to adjust the expression accordingly, but the general principle will be the same.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, descending is a keyword only working in conjunction with a comma-separated list of expressions as sort order modifier for each single expression, not within an expression, and that modifier also only is allowed in SQL and as overall sort order modifier of an index tag. Therefore you get a syntax error, yes.

You have even more problems in comparison with databases allowing "real" composite keys, when field types differ you have to convert to one type you can concatenate in VFP, mostly char, though you can also concatenate two small at max 4 digit long numbers with num1*10000+num2. In other databases, you simply specify field1,field2,field3 and then can also add in DESCENDING after field2, for example. In VFP you specify one expression, not a list of fields. The + is not a weird way of VFP to put commas into an index expression, it simply does what plus also does when you compute that expression eg in var=class + gender + name, and it also fails, when class and name are string types and gender is logical.

Depending on the type a plus operator can mean concatenation or summation or not work with two incompatible types. Anyway, this makes it impossible to put in a DESCENDING at some fields, you don't specify a list of fields where you can specify ASC/DESC for each field, you specify one single expression and you have the full set of functions and operators, but neither a comma is an operator in a general expression (aside of separating parameters in a function call, for example), a comma typically separates two or more expressions (also in a parameter list) and DESCENDING and ASCENDING also are not operators, they are modifiers which only have a meaning in the context of a set of values/rows.

To get a name descending you would need to invert all letters via CHRTRAN(field,"abc...xyz","zyx...cba")

Bye, Olaf.

 

[ameedoo3000]

Thank you very very much Mr. Mickey Lewis
Always learn from you many, many and important.
Whatever thank you so much is not enough for you.

Ahmed

 

[ameedoo3000]

Thank you very much dear Olaf for the good addition and useful.