ColdFusion Query of Queries Order By Case Sensitivity Fun

Posted on July 15, 2006 | 6 comments

[lang_en] 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?
[/lang_en]
[lang_zh]
你有没有在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有没有别的办法?
[/lang_zh]

Share the post "ColdFusion Query of Queries Order By Case Sensitivity Fun"

  • http://www.ryanguill.com/blog/ Ryan Guill

    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.

  • http://www.renaun.com Renaun Erickson

    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.

  • Scott Stroz

    Did you try

    SELECT Name
    FROM myList
    WHERE type = ‘Dir’
    ORDER BY UPPER(Name)

  • http://www.renaun.com Renaun Erickson

    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.

  • http://www.ghidinelli.com Brian

    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/

  • Terran

    Thanks – I’d been fighting with that for an hour before I found your blog post, and that solved the problem perfectly!