Skip to main content
A
Anonymous
April 13, 2010
Question

QoQ UNION distincts

  • April 13, 2010
  • 3 replies
  • 1345 views

What I am trying to do is UNION 2 result sets and only get the distinct values (obviously). The query being ran is:

 <cfquery name="qryUseUnits" dbtype="query">
  SELECT OptionDesc,
         OptionValue
  FROM   qryUseUnits
  
  UNION
  
  SELECT   Unit AS OptionDesc,
           Unit AS OptionValue
  FROM     qMatSup
  ORDER BY OptionDesc
 </cfquery>

In the qryUseUnits table, one of the OptionDesc results is "Cubic Feet" where as for qMatSup, the value is "Cubic feet" (notice the difference in case). So when I dump the results of that query, I will get:

Cubic Feet

Cubic feet

Cubic Meters

...

The collation on the SQL Server is SQL_Latin1_General_CP1_CI_AS, and if I were to run this query directly on the server, I get distinct results for sure. However, with the case being different, CF doesn't seem to consider that distinct. Rather than change my SELECT statements to convert all column to upper- or lower-case, is there something different I can do with the query itself to get distinct values regardless of case?

    This topic has been closed for replies.

    3 replies

    A
    Anonymous
    April 13, 2010

    Ahh, alright. Well, I appreciate the replies. I guess if I really wanted to get rid of the duplicates, I could do something like:


     <cfquery name="qryUseUnits" dbtype="query">
      SELECT OptionDesc,
             OptionValue,
             UPPER(OptionDesc) AS Sort1,
             0 AS Sort2
      FROM   qryUseUnits
      
      UNION
      
      SELECT Unit AS OptionDesc,
             Unit AS OptionValue    ,
             UPPER(Unit) AS Sort1,
             1 AS Sort2
      FROM     qMatSup
      ORDER BY Sort1, Sort2
     </cfquery>

    This way, I would get the results in order, and the results from the 2nd table would always be after the first table, and then I could go through and get the unique values by making into a collection, but the extra effort involved over a lowercase character isn't worth it.

    Thanks all.

    A
    Anonymous
    April 14, 2010

    Well, I got it to work as planned. It was a bit cleaner than I had imagined doing it and it works so that's what matters.

    What I ended up doing was creating a list of all the "units" that were returned from qryUseUnits. I'd then use this list to determine which values not to select when doing a UNION to qMatSup.

    <cfset arMatUnits = listToArray(uCase(valueList(qryUseUnits.OptionValue)))>
    <cfquery name="qryUseUnits" dbtype="query">
    SELECT OptionDesc,
           OptionValue
    FROM   qryUseUnits
    UNION
    SELECT   Unit AS OptionDesc,
             Unit AS OptionValue
    FROM     qMatSup
    WHERE UPPER(Unit) NOT IN (<cfqueryparam value="#listAppend(arrayToList(arMatUnits), 0)#" list="true" cfsqltype="CF_SQL_VARCHAR">)
    </cfquery>
    A
    Adam Cameron.
    Inspiring
    April 13, 2010

    Rather than change my SELECT statements to convert all column to upper- or lower-case, is there something different I can do with the query itself to get distinct values regardless of case?

    No.  CF's QoQ processor is case-sensitive.  That's the long and the short of it.

    --

    Adam

    -
    -__cfSearching__-
    Inspiring
    April 13, 2010

    Nevermind.

    Message was edited by: -==cfSearching==-

    ilssac
    ilssac
    Inspiring
    April 13, 2010

    TristanLee wrote:

    CF doesn't seem to consider that distinct.

    Message was edited by: Ian Skinner Never mind

    A
    Anonymous
    April 13, 2010

    I understand that any query statement send to the DB engine is all handled at the DB level. However, I am doing a QoQ in this case, so when I am doing a UNION on 2 queries, how can you say it isn't doing the UNION? It certainly is. There is no interaction with the DB... which brings me to the point, or question rather, as to why I can doing a UNION on the database and get distinct values regardless of case, but when I do a UNION within a QoQ, case matters.

    -
    -__cfSearching__-
    Inspiring
    April 13, 2010

    TristanLee wrote:

    I understand that any query statement send to the DB engine is all handled at the DB level. However, I am doing a QoQ in this case, so when I am doing a UNION on 2 queries, how can you say it isn't doing the UNION? It certainly is. There is no interaction with the DB... which brings me to the point, or question rather, as to why I can doing a UNION on the database and get distinct values regardless of case, but when I do a UNION within a QoQ, case matters.

    Probably not what you want to hear, but it is because that is the way in memory queries were designed in CF.  For whatever reason, the creators decided to make them case sensitive. So that means UNION's as well as simple comparisons (like WHERE  OptionDesc = 'Cubic Feet'), etcetera .. are case sensitive.

    Update: Out of curiousity I ran a few tests and while it is technically possible, it is just not worth the major contortions required.

    Message was edited by: -==cfSearching==-

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices