?
Solved

Output a Query Column

Posted on 2002-07-18
7
Medium Priority
?
190 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
[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
  • 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
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
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…
Suggested Courses

771 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