Hi-
I am struggling with this, but I am pretty sure it can be done? I want to use
a cfswitch case in my query, and output the results with a cfswitch in the
rollup, but I keep getting ORA-00979: not a GROUP BY expression. Now, I know
you are supposed to have all the column names in the rollup, but I know I have
seen what I am trying to do here done. Here is my code, I had to change the
names of things due to where I work, so that is why the silly names! Hopefully
I didn't completely mess up the syntax. Any suggestions might spare me from
pulling out my hair!!
<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/SQL/message.cfm/messageid:2696
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
