Michele,
I love the funny names of the columns, but the funny names would also need
to be in your group by:
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,
Thus:
GROUP BY ROLLUP
(
<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
)
The only columns you don't put in the generated on for count(*) as this is
the grouping column.
Make sure that you have the correct palcement of commas as that will cause
this issue as well.
Teddy
On 1/26/07, Michele Michele <[EMAIL PROTECTED]> wrote:
>
> 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:267720
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
