query of queries column names with spaces

Page 1 of 1

4 Replies - 8065 Views - Last Post: 07 November 2011 - 11:12 AM Rate Topic: -----

#1 Guest_gebuh*


Reputation:

query of queries column names with spaces

Posted 17 February 2010 - 01:44 PM

Hi all, ColdFusion MX7
I have a query I need to remove a bunch of columns from a sqlServer query for generating reports. Most of the columns have spaces in the names. I tried a QofQ using a string to hold the column names:
<cfset sysDevStr = "'PROJECT DATE','R CODE','REGION'" />
'cfquery name=chgQry dbtype="query"> 					 
SELECT #sysDevStr# 					 
FROM localQry 			 	
</cfquery>'

results in: Query Of Queries syntax error.
Encountered ""\'\'. Incorrect Select Statement, Expecting a 'FROM', but encountered '''' instead, A select statement should have a 'FROM' construct
when I try:
'cfquery name=chgQry dbtype="query"> 					 
SELECT * 					 
FROM localQry 			 	
</cfquery>'

I get the data, but that's not helping me cut out the unneeded columns
when I try:
'cfquery name=chgQry dbtype="query"> 					 
SELECT 'Project date' 					 
FROM localQry 			 	
</cfquery>'

or SELECT #PreserveSingleQuoutes(sysDevStr)# I get a table full of entries that say exactly whatever the column names are.
I've tried surrounding the column names with brackets, brackets with double quotes, back-tics, etc, with no luck. What am I doing wrong? How do I get a query with a subset of column data?
thanx

Is This A Good Question/Topic? 0

Replies To: query of queries column names with spaces

#2 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: query of queries column names with spaces

Posted 18 February 2010 - 05:40 AM

You may just want to rename the columns in the database to space-less names. You are going to run into these problems constantly if you have spaces.
Was This Post Helpful? 0
  • +
  • -

#3 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1546
  • View blog
  • Posts: 2,990
  • Joined: 13-January 08

Re: query of queries column names with spaces

Posted 24 February 2010 - 07:24 AM

Since you're having an issue with the QofQ, you might try amending the original query (that pulled from the database) and simply renaming the columns to something the QofQ won't have later trouble with.

It'd look something like:

select PROJECT DATE as project_date


That way, when it comes out you have a column name of a single word and if you think your issue is that QofQ doesn't like spaces in the column names...well...now you don't.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#4 Guest_gebuh*


Reputation:

Re: query of queries column names with spaces

Posted 24 February 2010 - 11:16 AM

thanx all for the suggestions.
Despite the documentation, query of queries does not like spaces, or dashes, or slashes, etc.
What I ended up doing was using regular expressions. Because my column titles are dynamic I can't hard code the names-I don't know what they'll be before they're generated. So I just regex'd all the column names after the initial query is run to replace all spaces with underscores, and all dashes with "zdashz". Then right before I create the excel document I run the regex function again to change them back. Of course I could have saved myself hours of work and done this earlier, but I thought I was doing something wrong.

View PostCraig328, on 24 February 2010 - 06:24 AM, said:

Since you're having an issue with the QofQ, you might try amending the original query (that pulled from the database) and simply renaming the columns to something the QofQ won't have later trouble with.

It'd look something like:

select PROJECT DATE as project_date


That way, when it comes out you have a column name of a single word and if you think your issue is that QofQ doesn't like spaces in the column names...well...now you don't.

Good luck!

Was This Post Helpful? 0

#5 mboea  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 07-November 11

Re: query of queries column names with spaces

Posted 07 November 2011 - 11:12 AM

View Postgebuh, on 24 February 2010 - 11:16 AM, said:

thanx all for the suggestions.
Despite the documentation, query of queries does not like spaces, or dashes, or slashes, etc.
What I ended up doing was using regular expressions. Because my column titles are dynamic I can't hard code the names-I don't know what they'll be before they're generated. So I just regex'd all the column names after the initial query is run to replace all spaces with underscores, and all dashes with "zdashz". Then right before I create the excel document I run the regex function again to change them back. Of course I could have saved myself hours of work and done this earlier, but I thought I was doing something wrong.

View PostCraig328, on 24 February 2010 - 06:24 AM, said:

Since you're having an issue with the QofQ, you might try amending the original query (that pulled from the database) and simply renaming the columns to something the QofQ won't have later trouble with.

It'd look something like:

select PROJECT DATE as project_date


That way, when it comes out you have a column name of a single word and if you think your issue is that QofQ doesn't like spaces in the column names...well...now you don't.

Good luck!


Can you post exactly how you did this? I have the same problem.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1