Skip to main content
D
Dan_Bracuk
Inspiring
August 27, 2009
Answered

Value List Q of Q Question

  • August 27, 2009
  • 1 reply
  • 925 views

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?

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    <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"]) />

    1 reply

    -
    -__cfSearching__-Correct answer
    Inspiring
    August 27, 2009

    <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"]) />

    D
    Dan_BracukAuthor
    Inspiring
    August 27, 2009

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

    -
    -__cfSearching__-
    Inspiring
    August 27, 2009

    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#">

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