You have to include category in your group by statement if you're
selecting it. And you need it in the view because you are limiting the
resultset based on it. Can you change your cfquery to something like:
<cfquery name="qryGetYear" datasource="#request.site.dsn#">
SELECT LEFT(PublicationYear, 4) AS Year, sum(PBID) as PBID
FROM dbo.PsycBOOKS
WHERE (IsNumeric(PublicationYear) = 1)
AND (Active = 1)
<cfif categories is not "">
AND Category in (<cfqueryparam
cfsqltype="integer" list="true" value="#x#" />)
</cfif>
GROUP BY LEFT(PublicationYear,4)
ORDER BY Year desc
</cfquery>
Haven't tested this of course, but you get the idea?
Cheers,
Kris
> What this returns is
>
> 2007 | 14 | C
> 2007 | 2| A
>
> What I need is
>
> 2007 | 16 |
>
> If I pull category out of the group by it errors out.
>
> I can't remove the Category because of this:
>
> <cfquery name="qryGetYear" datasource="#request.site.dsn#">
> SELECT LEFT(PublicationYear, 4) AS Year, PBID, Category
> FROM dbo.PsycBOOKS
> WHERE (IsNumeric(PublicationYear) = 1)
> AND (Active = 1)
> <cfif categories is not "">
> and Category in ('0'
> <cfloop list="#categories#"
> index="x">,'#x#'</cfloop>)
> </cfif>
> Order By Year desc
> </cfquery>
>
> The category is passed to the query from a form. It can be a,c or e or any
> combination
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289179
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
