?
Solved

CFOUTPUT QUERY (column names contains spaces)

Posted on 2005-04-05
10
Medium Priority
?
1,738 Views
Last Modified: 2013-12-24
OK, I'm trying to run a select query from a database that I'm connecting to (of which I have no control over the table design).

The column name is TestCode.

My query is as follows:

<cfquery name="test_query" datasource="dsn">
select TestCode from TestProc where TestITN=1
</cfquery>

<cfdump var="#test_query#"> returns the following:

Test Code
ABC

Notice the space between Test and Code.  I find out that it's not returning the column name (in the column list), rather the column description field from the database, which is no good.

So, I try to alias the column (ie, select TestCode as test_code) ...nothing.  It seems to ignore my alias and returns "Test Code" each time (which again appears to be the description in the db for the column) which I cannot reference when looping the recordset.

Any ideas as to how I can get to the data?  Why would my alias not be working?

Please help...Thanks.
0
Comment
Question by:gtfly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13708095
Try this

<cfquery name="test_query" datasource="dsn">
select TestCode AS MyCode
from TestProc where TestITN='1'
</cfquery>

<cfoutput query="test_query">
#MyCode#<br>
</cfquery>


<cfdump var="#test_query#">
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13708129
You say that the name of the column in the "TestProc" table you're trying to retrieve data from is "TestCode" right?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13708263
Yes try an alias like trailblazzy suggested.  Also as a note you can access columns with spaces in the name like this:

<cfoutput query="test_query">
#[Test Code]#<br>
</cfquery>

or this:

<cfoutput query="test_query">
#test_query["Test Code"]#<br>
</cfquery>

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:gtfly
ID: 13709362
Yes, the name of the column is TestCode, but the description is "Test Code"...I tried the alias again as you've suggested, but it still does not work (gives cf column not found message).

Also tried mrichmon's two suggestions, but they did not work either.  The 2nd option #test_query["Test Code"]# gave me an error: Complex object types cannot be converted to simple values.

Any other ideas?  I've never had this problem with any other database, and this db is a little strange, as it's an old MUMPS database converted to a relational db.....

thanks for the help thus far.
0
 

Author Comment

by:gtfly
ID: 13709389
One other thing to note...When running the query:

select TestCode as MyCode from TestProc where TestITN='1'

in another app (WinSQL), it returns the TestCode value as MyCode correctly...so it seems that the sql statement is correct, it's just that CFMX thinks the column descriptions are the column names, and ignores my 'AS' and alias completely.
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13709448
I'm pretty sure there's an issue then with your datasource, you may want to make sure your connection is good.

Try..

SELECT * FROM TestProc WHERE TestITN='1'

and try

<cfdump var="#datasource#">
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13709568
I guess I posted right after yours, try changing the column name to a different value and see if that works? there shouldn't be that issue, but at least you could rule out naming as your problem? Don't add spaces in your column names either, think you don't have that issue, but worth a mention...
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 13709608
try this:

<cfloop from="1" to="#test_query.RecordCount#" index="i">
#test_query["Test Code"][i]#<br>
</cfloop >
0
 

Author Comment

by:gtfly
ID: 13709692
BINGO!  Thanks mrichmon!  The cfloop tag worked great...

Thanks to you as well trailblazzyr55 for your ideas...
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 13709762
Glad on of us could get you on the right direction :o)

Regards,
~trail
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question