Solved

Output a Query Column

Posted on 2002-07-18
7
179 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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