If I may offer some CONSTRUCTIVE answers like John, Query of Queries are very limited in their abilities compared to what modern databases will do. I believe they are even behind the basic mySQL so I would recommend using stored procedures or functions instead of QoQ. In addition, I would agree with John and recommend replacing the spaces with underscores. Since you may be inheriting a database you didn't build or just leery of changing column names then here is some cfquery code that will make the replacement for a basic SELECT * FROM query. (NOTE: I am using SQL2005 so adjust according to your database)
<cfquery name="qryResult" datasource="#application.dsn#"> declare @colName varchar(50) ,@sqlstr varchar(MAX) ,@colNum int ,@tableName varchar(50) set @tableName = 'tbl_test' set @colNum = 1 set @sqlstr = 'SELECT ' SELECT @colName = COL_NAME(OBJECT_ID(@tableName), @colNum) while @colName IS NOT NULL BEGIN if(@sqlstr <> 'SELECT ') set @sqlstr = @sqlstr + ', ' set @sqlstr = @sqlstr + '['[EMAIL PROTECTED]'] as '+ replace(ltrim(rtrim(@colName)),' ','_') set @colNum = @colNum+1 SELECT @colName = COL_NAME(OBJECT_ID(@tableName), @colNum) END set @sqlstr = @sqlstr + ' FROM '[EMAIL PROTECTED] <!--- INSET WHERE CLAUSE HERE ---> exec(@sqlstr) </cfquery> <cfdump var="#qryResult#"> Just replace your table name with tbl_test. Of course you could put it in a stored procedure as well. Tommy Geist --- Douglas Knudsen <[EMAIL PROTECTED]> wrote: > if( self is DBA ) { > smile(); > smack(SMACK_CONSTANTS.SOFTLY); > } else { > for( var i:int; > i<ArrayOfFieldsWithSpacesInNames.length;i++) { > smack(SMACK_CONSTANTS.REALLYHARD); > } > } > > :D > > DK > On Thu, Apr 17, 2008 at 4:48 PM, John Mason > <[EMAIL PROTECTED]> wrote: > > > It's annoying that spaces were ever allowed in > the first place. I > > would do something like this in the first cfquery > so it will work in your > > qoq > > > > select... [Column Name] as ColumnName > > > > A better solution frankly is to eliminate those > spaces > > > > John Mason > > [EMAIL PROTECTED] > > 770.337.8363 > > > > www.FusionLink.com - ColdFusion and Flex hosting > > Now offering VPS Plans running with VMware > technology > > Now offering ColdFusion 8 Enterprise hosting > > FREE Subversion hosting > > > > ------------------------------ > > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > *On Behalf Of *Scott > > Councill > > *Sent:* Thursday, April 17, 2008 4:25 PM > > *To:* discussion@acfug.org > > *Subject:* [ACFUG Discuss] QoQ question > > > > I am writing a query of query where the result > set in the original query > > has spaces in the field names. I know when you > are querying a database, you > > use [Field Name] as the syntax but CF is throwing > an error when I do this > > with the QoQ. Any ideas how I can call the > fieldname with spaces in a QoQ? > > > > > > > > > > > > *J. Scott Councill * > > Software Engineer II > > Direct: 404.601.4390 > > Cell: 804.267.9555 > > [EMAIL PROTECTED] > <[EMAIL PROTECTED]> > > > > *Spunlogic > > *Expect More From the Web > > www.spunlogic.com > <http://www.spunlogic.com/?CMP=EMC-OutlookSig> > > > > *Looking for insights and tips on all aspects of > interactive marketing? > > Visit Spunlogic's Blog > <http://www.spunlogic.com/blog/?CMP=EMC-OutlookSig>for > a look inside the minds of a leading interactive > agency. > > * > > > > > > > > > ------------------------------------------------------------- > > Annual Sponsor - Figleaf Software > <http://www.figleaf.com> > > > > To unsubscribe from this list, manage your profile > @ > > http://www.acfug.org?fa=login.edituserform > > > > For more info, see > http://www.acfug.org/mailinglists > > Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > > List hosted by FusionLink > <http://www.fusionlink.com> > > > ------------------------------------------------------------- > > > > > ------------------------------------------------------------- > > Annual Sponsor - Figleaf Software > <http://www.figleaf.com> > > > > To unsubscribe from this list, manage your profile > @ > > http://www.acfug.org?fa=login.edituserform > > > > For more info, see > http://www.acfug.org/mailinglists > > Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > > List hosted by FusionLink > <http://www.fusionlink.com> > > > ------------------------------------------------------------- > > > > > > -- > Douglas Knudsen > http://www.cubicleman.com > this is my signature, like it? > > > > ------------------------------------------------------------- > Annual Sponsor FigLeaf Software - > http://www.figleaf.com > > To unsubscribe from this list, manage your profile @ > > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------