Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Output a Query Column

Posted on 2002-07-18
7
Medium Priority
?
193 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 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