Value List Q of Q Question

[Dan_Bracuk]

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?

[-__cfSearching__- · Accepted solution]

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

[Dan_Bracuk]

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

[-__cfSearching__-]

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