ColdFusion Query of Queries Order By Case Sensitivity Fun
July 15th, 2006 中文
Have you ever tried using ORDER BY in a query of queries in ColdFusion, well come to find out its case sensitive, and I need it to be case insensitive.I have a query of queries in ColdFusion. In Windows my original query sorted it self out just fine, because it was listing the underlying File system items which are case insenstive. When I deployed the code on Linux of course the original query sorts differently. The code actually does another query on the first query to add some other fields. But when I did a ORDER BY Name on this query of queries it was Case Sensitive, and thus a capital "Z" came before a lowercase "a". You can not use the UPPER or LOWER query functions on the ORDER BY clause but you can use them when defining a field.
This led me to the solution below.
-
SELECT Name,
-
UPPER( Name ) as DName
-
FROM myList
-
WHERE type = 'Dir'
-
ORDER BY DName
Is there a built in way in ColdFusion to do this with out create a new column on the query?
Entry Filed under: ColdFusion MX 7
6 Comments
1. Ryan Guill | 2006-07-15 at 7.28 pm
Since it looks like all you are doing is selecting a subset of data from a previous query, could you order by your original query by name? then you would not need to order by again in your q of q…
this is interesting though. Are you sure its because of the operating system that it is ordering this way or is it because of the original database settings? either way I suppose if you did order by name in your q of q it should do it right… Strange.
2. Renaun Erickson | 2006-07-15 at 9.58 pm
Its because the query is coming from which does not have a case insensitive sort mechanism. The underlying OS file system treats the files/directories different and thus sorts them by default differently.
3. Scott Stroz | 2006-07-16 at 9.00 pm
Did you try
SELECT Name
FROM myList
WHERE type = ‘Dir’
ORDER BY UPPER(Name)
4. Renaun Erickson | 2006-07-16 at 9.03 pm
Thanks,
Thats exactly what I tried first and it failed for me (using ColdFusion MX 7.0.2), that left me with the solution above.
5. Brian | 2006-08-17 at 1.02 pm
Be careful - depending on your data, using upper/lower may throw errors as part of these issues: http://www.ghidinelli.com/2006/08/08/wrangling-qofq-datatypes/
6. Terran | 2007-08-14 at 8.19 am
Thanks - I’d been fighting with that for an hour before I found your blog post, and that solved the problem perfectly!