What is the output you get when you do it this way:
<cfsavecontent variable="RainvilleQueryString">
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 Len(detailType)> AND
(e.DETAIL_TYPE LIKE'#detailType#')</cfif><cfif Len(State)> AND
(e.STATE IN (#preservesinglequotes(State)#))</cfif><cfif
Len(County)>AND
(e.COUNTY IN (#preservesinglequotes(County)#))</cfif><cfif
Len(CTV_COUNTY)>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>
</cfsavecontent>
<cftry>
<cfquery name="Results"
datasource="#Request.INFO_DSN#">#RainvilleQueryString#</cfquery>
<cfcatch type="Any"><cfoutput>
<pre>#RainvilleQueryString#</pre>
<br/>
#displayDBError(CFCATCH.sql,CFCATCH.detail)#
</cfoutput></cfcatch>
</cftry>
On 1/26/07, Michele Rainville <[EMAIL PROTECTED]> wrote:
> 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!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:2710
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
