Hi-
I am trying to incorporate a switch statement in my rollup, however I get the
ORA-00979: not a GROUP BY expression error. Now, I know you have to include
all column names in your rollup, but I know I have seen what I am trying to do
done before. Here is my code, I had to change some of the names, as you can
see. Any suggetions or recommendations? I sure would appreciate it!!
<cftry>
<cfquery name="Results" datasource="#Request.INFO_DSN#">
SELECT <cfswitch expression="#Sort#">
<cfcase value="State">State</cfcase>
<cfcase value="County">County</cfcase>
<cfcase value="CTV_County">CTV_County</cfcase>
</cfswitch>,
e.NUM_HOURS, e.NUM_SOCKS, e.NUM_SHOES, e.NUM_STATE, e._LAST,
e.LOCATION,
e.DRE_ASSIGNED, e.AUDIENCE_SIZE, e.I_LOVE_FRIDAYS, e.ITS_COLD,
e.THIS_QUERY_IS_ON_MY_NERVES, e.I_AM_STUBBORN,
e.I_DONT_WANT_TO_ASK_OUR_DB_GUY,
count(*) totals
From SCOOBY.ENF_DETAIL e
left join SCOOBY.prd_header ph on e.prdkey = ph.prdkey
where DETAIL_DATE between to_date('#dateFrom#','mm/dd/yyyy') and
to_date('#dateTo#','mm/dd/yyyy') AND ph.status in ('1')
<CFIF #detailType# NEQ "">
AND e.DETAIL_TYPE LIKE'#detailType#'
</CFIF>
<CFIF #State# NEQ "">
AND e.State IN (#preservesinglequotes(State)#)
</CFIF>
<CFIF #County# NEQ "">
AND e.COUNTY IN (#preservesinglequotes(County)#)
</CFIF>
<CFIF #CTV_COUNTY# NEQ "">
AND e.CTV_COUNTY IN (#preservesinglequotes(CTV_County)#)
</CFIF>
Group by ROLLUP(
<cfswitch expression="#Sort#">
<cfcase value="State">
State
</cfcase>
<cfcase value="County">
COUNTY
</cfcase>
<cfcase value="CTV_County">
CTV_COUNTY
</cfcase>
</cfswitch>)
<cfswitch expression="#Sort#">
<cfcase value="State">
ORDER BY State
</cfcase>
<cfcase value="County">
ORDER BY COUNTY
</cfcase>
<cfcase value="CTV_County">
ORDER BY CTV_COUNTY
</cfcase>
</cfswitch>
</cfquery>
<cfcatch type="database">
<cfoutput>#displayDBError(CFCATCH.sql,CFCATCH.detail)#</cfoutput>
</cfcatch>
</cftry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267717
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
