CFOUTPUT QUERY (column names contains spaces)

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.
gtflyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

trailblazzyr55Commented:
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
trailblazzyr55Commented:
You say that the name of the column in the "TestProc" table you're trying to retrieve data from is "TestCode" right?
0
mrichmonCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

gtflyAuthor Commented:
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
gtflyAuthor Commented:
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
trailblazzyr55Commented:
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
trailblazzyr55Commented:
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
mrichmonCommented:
try this:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gtflyAuthor Commented:
BINGO!  Thanks mrichmon!  The cfloop tag worked great...

Thanks to you as well trailblazzyr55 for your ideas...
0
trailblazzyr55Commented:
Glad on of us could get you on the right direction :o)

Regards,
~trail
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.