Grouping question

Eric Bourland
Eric Bourland used Ask the Experts™
on
ColdFusion 9
MS SQL Server 2005

Hi. My CFOUTPUT does not group on column PublicationYear the way that I think it should. =) How can I edit my query or my output to get this result:

"CEP scheduled no meetings in year 2011."

rather than this result:

"CEP scheduled no meetings in year 20112011201120112011201120112011201120112011201120112011201120112011."

I am guessing I cannot group variable #url.selectedYear# on column PublicationYear because variable #url.selectedYear# is not referenced in query getDocumentYears.

Thanks as always for advice. I've been puzzling over this task for a while today.

Eric

Query, getDocumentYears:

<!--- query to select document publication years --->
<cfquery name="getDocumentYears" datasource="#application.datasource#">
SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear DESC
</cfquery>

Output:

<p>CEP scheduled no meetings in year <cfoutput query="getDocumentYears" group="PublicationYear" groupcasesensitive="no">#url.selectedYear#</cfoutput>.</p>

Example: http://www.cep-dc.org/meetings/index.cfm?selectedYear=2011

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
<cfoutput query="getDocumentYears" ....

I think that's the wrong query.  IIRC your page has 2 queries.  One that lists ALL available years. The other lists all documents for the #url.selectedYear#.  I think you should be using the 2nd query instead.  But use the recordCount to determine if any documents were found

ie
<cfif getDocuments.recordCount eq 0>
     CEP scheduled no meetings in year #url.selectedYear# ....
</cfif>

Author

Commented:
That was it. I was wondering if it might be the other query. This worked:

<cfif ...>
...

<cfelseif getDocumentsandFiles.recordCount eq 0>

<p>CEP scheduled no meetings in year <cfoutput>#url.selectedYear#</cfoutput>.</p>

</cfif>

Author

Commented:
Thank you as always, _agx_. Hope you are well.

Eric

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial