Indexing

[alisaif]

Hi,

I want to generate index as follows

Cursor Fields
-------------

1. PCODE ( Customer Code )
2. FULL_NAME ( Customer Name )
3. LOCATION ( Address )
4. NETSALE ( Total Net Sale )

Now Index should be in descending order based on field TOTSALE but I want to keep Customer in Alphabetical Order, can I do so?
I did this but the it is showing the last Customer (Alphabetically) to the top of the list.

INDEX on pcode+location+STR(netSale) TAG NetAsc  ASCENDING ADDITIVE 
INDEX on pcode+location+STR(netSale) TAG NetDesc DESCENDING ADDITIVE

 

[Olaf Doschke]

First: In the general case an index can be used for both ascending and descending order, so you don't need to define it twice. You later SET ORDER TO TAG Net ASCENDING or SET ORDER TO TAG Net DESCENDING

In this case you have a specialty in wanting the customer alphabetical in all cases. Before going into details, there is a main problem here: You're not even including the customer name in the index. The order of customer names is random, first sort priority is pcode in all cases, everything else is secondary.

If you want to order by something ascending and another field descending, you can define an index like cField1+CHRTRAN(Lower(cField2),"abc...","zyx...") (you get the idea, reversing letters). You can't define an index with different sort orders on single fields, but you can inverse the order by chrtran.

One thing is for sure: If you want data in customer name order in the first place, you can't have them in pcode order at the same time. It would be nonsense of course, to sort customer name and pcode individually, because that would yield wrong combinations of pcode and customer.

You can do this easier with SQL ORDER BY clause, as that allows individual ASC or DESC order per column you specify in the order list. But both an INDEX and ORDER BY will keep records together, you never sort individual columns, indexes and order will always only change order of complete records, unlike Excel, where you may only sort an individual column without also moving data of other columns alongside.

Do you really want to reorganise pcodes for customers? Isn't pcode a primary key in your case? Those should never have a meaning or a sort order relevance at all.

Bye, Olaf.

 

[Mike Lewis]

In general, if you want to mix an ascending key with a descending key, you can do as follows:

I'll assume that you want to show descending sales within ascending customers. In other words, yo want the result to look something like this:

Alfred     5000
Alfred     4500
Bell       2000
Charlie    6200
Charlie    6100
Charlie    5700

To achieve that, create your index like this:

INDEX ON pcode + TRANSFORM(999999999 - NetSales, "999,999,999")

That said, the preferred approach would be to create single indexes, on pcode and Netsales respectively, and to use a SELECT statement with an ORDER BY clause to get them into order:

SELECT * FROM TheTable ORDER BY pcode, Netsales DESC INTO CURSOR ...

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks a lot Sir !!!!

I want exactly the same as you mentioned, I applied and got the result

Saif

 

[craigber]

Actually, Mike, the preferred way is to never do SELECT *, but list the fields, even if you want all of them. This is DBA101 stuff.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[DSummZZZ]

...the preferred way is to never do SELECT *...

Yeah, where applicable. But if I had to list every single field every time I did a one-off query, I would probably either have written an app to concatenate fields for me, quit writing queries, or slammed a keyboard into a table top.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

the preferred way is to never do SELECT *

Agreed - provided you know the names of all the fields at design time (which, in this case, I don't).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Hi,

Mr.Mike, Please refer to my previous thread, can I index on date as shown below like Amount.

INDEX ON pcode + TRANSFORM(999999999 - NetSales, "999,999,999")

Alfred     	{12/12/2013}
Alfred    	{01/11/2013}
Bell      	{17/12/2013}
Charlie    	{28/12/2013}
Charlie   	{10/12/2013}
Charlie   	{01/12/2013}

Please guide.

Thanks

Saif

 

[Mike Lewis]

Saif,

So, you want to index on a descending date within an ascending string? If so, the principle is the same as what I showed you for a numeric, except that you use a high date (such as {^2999-12-31}) rather than a high number (999999999):

INDEX ON pcCode + TRANSFORM({^2999-12-31} - TheDate, "999999") TAG CodeDate

where TheDate is the name of the date field, and CodeDate is the name I have given to the index tag.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks Mike, I got it with your help.
As far as HIGH DATE is concern, no problem I will ask you that time .

Thanks

Saif

 

[Olaf Doschke]

To make sure, you can go up to 9999

Bye, Olaf.