3 Replies Latest reply: Aug 27, 2009 3:02 PM by -==cfSearching==- RSS

    Value List Q of Q Question

    Dan Bracuk Community Member
    Dan Bracuk Aug 27, 2009 11:53 AM

      I run a Stored Procedure which brings back a query object.  Some of the column names of that query object have two words.  For example, let's say a field was called "one two".   I know I can output this data with array notation

       

      #queryname["one two"][currentrow]#

       

      but what I really want to do is to create a value list from that field, run another database query based on that list, and then do a Q of Q, using "one two" in the where clause.

       

      Everything I have tried so far with both valuelist () and Q of Q has resulted in a syntax error.  Has anyone ever successfully accomplished this?

      • 636 Views
      • Tags:
        • 1. Re: Value List Q of Q Question
          -==cfSearching==- Community Member
          -==cfSearching==- Aug 27, 2009 12:40 PM (in response to Dan Bracuk)

          <cfoutput>
              #arrayToList(q1["one two"])#
          </cfoutput>

           

          That would work for the valueList. I do not know of any way to reference an invalid column name directly in a QoQ.  But you could always rename the column so to speak ..

           

               <cfset queryAddColumn(myQuery, "NewColumnName", myQuery["one two"]) />

          • 2. Re: Value List Q of Q Question
            Dan Bracuk Community Member
            Dan Bracuk Aug 27, 2009 2:49 PM (in response to -==cfSearching==-)

            I ended up using QueryAddColumn.  I was wondering if I could work with the original query.

            • 3. Re: Value List Q of Q Question
              -==cfSearching==- Community Member
              -==cfSearching==- Aug 27, 2009 3:02 PM (in response to Dan Bracuk)

              If you use QueryAddColumn, you can still use the original query.  But I do not know of any way to reference the invalid column name in a QoQ. AFAIK, you must use the renamed column instead

               

              <cfquery name="myQuery" datasource="SomeDSN">
                  SELECT 'b' AS [One Two] UNION
                  SELECT 'c' AS [One Two] UNION
                  SELECT 'd' AS [One Two] UNION
                  SELECT 'a' AS [One Two]
              </cfquery>

               

              <cfset queryAddColumn(myQuery, "NewColumnName", myQuery["one two"]) />

               

              <cfquery name="newQuery" dbtype="query">
                  SELECT    *
                  FROM    myQuery
                  WHERE    NewColumnName LIKE '%something%'
              </cfquery>
              <cfdump var="#newQuery#">