Solved

Output a Query Column

Posted on 2002-07-18
7
177 Views
Last Modified: 2013-12-24
Hi,
     I've created a crosstab query which uses a Date field as column headings. I was able to output the column names OK using listgetat(theQuery.ColumnList,1). However, I am unable to output the data for the list. Here's my query:

<cfquery name="crossTMResults" datasource="TM">
     TRANSFORM Count(ACTIVITIES.ACTIVITYID) AS CountOfACTIVITYID
     SELECT ACTIVITIES.TM_RESULTS
     FROM ACTIVITIES
     WHERE ACTIVITYDATE BETWEEN #createodbcdatetime(StartDate)# AND #createodbcdatetime(theenddate)#
     GROUP BY ACTIVITIES.TM_RESULTS
     PIVOT ACTIVITYDATE;
</cfquery>

I've tried using evaluate, but I get an error, for example that says that it doesn't know what crossTMResults.7/16/02 is. I also tried crossTMResults.[7/16/02] and a few others. Does anyone know how I can specify the query column I want the data for?

TIA,

:) David
0
Comment
Question by:dapperry
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Expert Comment

by:CFXPERT
ID: 7163106
what does it give you when you output like this?

<cfoutput query="crossTMResults">
#TM_RESULTS#
</cfoutput>

KWK
0
 
LVL 17

Expert Comment

by:anandkp
ID: 7164048
<cfset x = VALUELIST(crossTMResults.ACTIVITYDATE)>

once u get it in a list u can proceed with ur code

try this & let me know

K'Rgds
Anand
0
 
LVL 3

Author Comment

by:dapperry
ID: 7164797
CFXPERT,

I get the row values:

Bad Number,Voice Mail,No Answer,No longer with Company, etc.

anandkp,

There is no column in the query results for ACTIVITYDATE. The only columns are, for example:

"7/16/02","7/17/02","7/18/02","TM_RESULTS"

The question is how to refer to the columns that have a date as the column name queryname.7/16/02 doesnt work.
In that regard, how do you refer to columns that have a space in them? If you have a field called "Num Employees" you couldn't use queryname.Num Employees. How would you refer to that. Your help is, and will be, greatly appreciated.


:) David
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Expert Comment

by:anandkp
ID: 7164896
Hi there,

I doubt if u can create columns with spaces in b/w them
but ne-ways if u have them - the u can try this.

<cfset spacefield = "num employees">

<cfset x = Evaluate("VALUELIST(crossTMResults.#spacefield#)")>

then u can use this list as u want.

let me know

K'Rgds
Anand

0
 
LVL 3

Author Comment

by:dapperry
ID: 7180610
I guess I solved the dilemma myself by using a format in the query. Here's what I ended up with:

<cfquery name="crossTMResults" datasource="TM">
                         TRANSFORM Count(ACTIVITIES.ACTIVITYID) AS CountOfACTIVITYID
                         SELECT ACTIVITIES.TM_RESULTS
                         FROM ACTIVITIES
                         WHERE ACTIVITYDATE BETWEEN #createodbcdatetime(StartDate)# AND #createodbcdatetime(theenddate)#
                         GROUP BY FORMAT(ACTIVITIES.TM_RESULTS,'zmmddyyyy')
                         PIVOT ACTIVITYDATE;
                     </cfquery>

I was then able to to refer to the column, by using evaluate and a listgetat on the query.Columns list. Thanks anyway, I'll probably have this question deleted if there are no objections.

:) David
0
 
LVL 5

Expert Comment

by:CFXPERT
ID: 7180799
Sounds fine to me.  Good job in figuring this one out on your own.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7181651
User resolved; points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now