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:* [email protected]
> > *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
-------------------------------------------------------------
