ColdFusion Query of Queries Order By Case Sensitivity Fun
July 15th, 2006 英文
你有没有在Cold Fusion的query of queries试过用ORDER BY?
我发现是分大小写但是我要不分大小写。我有一个在Cold Fusion的query of queries。因为Windows的filesystem是不分大小写的,我原来的query排得很好,但用Linux的时候,很显然原来的query排得不一样。 其实,这个代码,为了加上其他的字段 ,做出另外一个query。但是在这个query of queries 做出 ORDER BY Name, 它是分大小写,因此大”Z” 比小”a”早排出来。当你要定义字段,你可以用UPPER或LOWER功能, 但是ORDER BY不行。
解答如下:
除非你在query造新行,ColdFusion有没有别的办法?
Entry Filed under: ColdFusion MX 7
6 Comments
1. Ryan Guill | July 15th, 2006 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 | July 15th, 2006 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 | July 16th, 2006 at 9:00 pm
Did you try
SELECT Name
FROM myList
WHERE type = ‘Dir’
ORDER BY UPPER(Name)
4. Renaun Erickson | July 16th, 2006 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 | August 17th, 2006 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 | August 14th, 2007 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!