Ordering character fields 1

[Bryan - Gendev]

A user has found a 'bug' in my application

I have a field (fname) consisting of filenames with paths ( some very long) in a table used to report from.

As an option in my reporting module I want to create the list of records ordered on the filename. I have this:-

Select * From (mytable) Into Cursor mycursor Order By fname

This works but puts the uppercase filenames in the list before the lowercase ones.

I have tried upper(fname) and lower(fname) for the order but this fails leaving the records in their default order.

Can someone suggest an expression to get the records in 'true' alphabetic order regardless of case?

Thanks

GenDev

 

[Olaf Doschke]

Why would uper not work, how do you apply it? You can't have expression in Order, that wouldn't leave the records in default order, that would error!

Create Cursor curTest (cField C(4))
Insert into curTest Values ("axyz")
Insert into curTest Values ("Abc")
Insert into curTest Values ("bcd")
Insert into curTest Values ("Bxyz")

Select Upper(cField), * from curTest Order By 1 Into Cursor curSorted Readwrite

Or create an index on Upper(cField) on the result cursor.

In this case Abc sorts before axyz as you'd need it, and Bxyz stays after bcd, of course.

Bye, Olaf.

 

[Bryan - Gendev]

I tried both methods which didn't mix the upper and lower case filenames. So I did both ..

	Select Upper(fname),* From (mytable) Into Cursor mycursor Order By fname
	INDEX on UPPER(fname) TO anything

Here is the result - what am I missing?

http://beesoft.soho.on.net/mysort.htm

Regards

GenDerv

 

[Mike Lewis]

Which column are you showing in your screen shot?

MyCursor will contain two copies of the fname. The first should be in uppercase, but will not affect the order. The second will be in mixed case (which is presumably what we are seeing in the shot); this is the one that will be used by the ORDER BY clause. The index will be on this second column, but it's not necessarily the controlling index.

Also, are you sure you are indexing the cursor, not the original table? I would have thought your index command would fail if you were indexing the cursor, on the basis that you have no READWRITE clause in the SELECT.

In summary, your first line of code should do what you want, regardless of the second line.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

In summary, your first line of code should do what you want, regardless of the second line.

Sorry, my mistake. That's not quite right. You need to do this:

Select Upper(fname) [b]AS Upper_Name[/b],* From (mytable) Into Cursor mycursor Order By [b]Upper_Name[/b]

Alternatively, ORDER BY 1 (as per Olaf's original suggestion).

Mike







__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Bryan - Gendev]

Mike,

Dealing with this code only

Select Upper(fname),* From (mytable) Into Cursor mycursor Order By fname

I only see one field fname in mycursor.

So I'm getting confused as to what is really happening here..?

Regards

GenDev

 

[Bryan - Gendev]

Our messages overlapped...

Thanks for your latest - that worked perfectly!!

Many Thanks

GenDev

 

[Olaf Doschke]

Order By fname was the mistake, of course. You want to order by the resulting expression in field 1, no matter if you name it or not, if you order by fname, you order by the non altered fname, of course...

Bye, Olaf.