Solved

Output a Query Column

Posted on 2002-07-18
7
184 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
json_decode return null? 8 104
Two nodes for updates and forwarding 8 51
.htacess file 301 redirects that are strange 4 75
How to use 2 separate DNS names. 5 51
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

738 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