[SOLVED] How to prevent Query of Queries returning case sensitive results from CFC in Coldfusion

How to prevent Query of Queries returning case sensitive results from CFC in Coldfusion

I have an issue where I'm joining two record sets from two different datasources using Ben Nadel's QueryAppend.cfc. The data I get back is correct but the ordering of the data is not expected. The combined result set is being sorted like so with all results starting with a lowercase letter appended to the bottom of the record set:

Screen grab of cfdump

I'm expecting (and need) the following type of sort:

Apple

art

bottle

Boy

Cat

coin

dart

Dog

Code follows:

<!---Calling Template--->

<cfquery name="getDataSet1" datasource="datasource1">
SELECT param1
FROM table1
</cfquery> 

<cfquery name="getDataset2" datasource="datasource2">
 SELECT param1
FROM table2
</cfquery> 

<cfscript>
// Create object
 TheUnionObject = createObject("component", "cfc/QueryAppend");
 // Call the function
myUnionResult = TheUnionObject.QueryAppend(getDataSet1, getDataSet2);
</cfscript> 

<!---Dump results--->
<cfdump var="#myUnionResult#">






 <!---QueryAppend.cfc--->
 <cfcomponent>
 <cffunction name="QueryAppend" access="public" returntype="query" 
 output="false"
 hint="This takes two queries and appends the second one to the first one. 
  Returns the resultant third query.">
 <cfargument name="QueryOne" type="query" required="true" />
 <cfargument name="QueryTwo" type="query" required="true" />
 <cfset var LOCAL = StructNew() />
 <cfquery name="LOCAL.NewQuery" dbtype="query">
       (
            SELECT
               *
            FROM
                ARGUMENTS.QueryOne
        )
    UNION 
        (
            SELECT
                *
            FROM
                ARGUMENTS.QueryTwo
        )  ORDER BY Param1 ASC
  </cfquery>
 <cfreturn LOCAL.NewQuery />
  </cffunction>
  </cfcomponent>

I'm assuming that this default sorting behavior is some under-the-hood ColdFusion code. Can anyone tell me how to effect a change to this default ORDER BY behavior?

Solution

A quick fix would be to add a field to your select statement that upper cases (upper) the field value you want to sort by and then order by that field, but still output the uncased field. Something along the lines of:

select   *, 
         upper(name) as upperName
from     query
order by upperName