Query of Query is sorting ALL-CAP names incorrectly.

[Smapty]

Hello,

I'm playing around with "query of queries" to lighten the load on our server and make the code a little more compact. Problem I've run into is that the method of ordering alphanumerics seems to change when ordering a call from a query of query as opposed to doing it directly from the database tables. Specifially, ALLCAPS names come before uppercase names. For example, EVERETT comes before Eackart... and Zigfield comes before applegate. I guess the coldfusion is treating all capital characters as a distict set that comes before all lowercase letters. Pretty stupid.

Anyway around this problem? Here's my code....

<CFQUERY Name = "DisplayAllAttendeesbyAuthNum" Datasource = "#sourcename#">
SELECT AttendeeInfo.RefNum, AttendeeInfo.FName, AttendeeInfo.LName, PayProccessed.PayAuthNum
FROM AttendeeInfo, PayProccessed
WHERE AttendeeInfo.RefNum = PayProccessed.RefNum AND PayProccessed.PayAuthNum <> ''
ORDER by PayProccessed.PayAuthNum
</CFQUERY>

<CFQUERY Name = "DisplayAllAttendeesbyAuthNumSortbyLName" dbtype="query">
SELECT *
FROM DisplayAllAttendeesbyAuthNum
ORDER by LName
</CFQUERY>

 

[GUJUm0deL]

Convert all characters into lower case. SQL has a function called LOWER that will change any text in a field into lowercase Provided you are allowed to change all characters into lowecase.

The syntax usage is: lower(columnName)

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[Smapty]

No, I'm not allowed to change the letters to lowercase as that would make reading the names too diffulcult to the user.

From what I've been able to find out, apparently the second query is treating the results as ASCII characters... which orders A-Z first and then a-z thereafter.

Anyway to tell teh CF query to treat the data as a "language"?

 

[DainBramage]

1) What's the point in having a second query that just selects everything from the first query? It's a waste of time and resources. Now you have 2 sets of the exact same info.

2) Have you tried "ORDER by LOWER(LName)"?

 

[GUJUm0deL]

DainBramage, that's what I suggested but smapty said he/she couldn't do that. But I do agree with you about using 2 queries that basically do the same thing.

smapty try using CF to order the results.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[DainBramage]

Um...if you use LOWER in the ORDER BY clause, it has no affect on the data or the way it's displayed, it just reads all records as lower case so you get an accurate comparison.