>From TFM... GROUPING SETS
GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. You specify just the desired groups, and Oracle does not need to perform the full set of aggregations generated by CUBE or ROLLUP. Oracle computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation. The UNION ALL means that the result set can include duplicate rows. Within the GROUP BY clause, you can combine expressions in various ways: * To specify composite columns, you group columns within parentheses so that Oracle treats them as a unit while computing ROLLUP or CUBE operations. * To specify concatenated grouping sets, you separate multiple grouping sets, ROLLUP, and CUBE operations with commas so that Oracle combines them into a single GROUP BY clause. The result is a cross-product of groupings from each grouping set. ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 4:32 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] CF, Oracle 9i, and GROUP BY GROUPING SETS I know, I know, RTFM, but it has walked off. What does "GROUP BY GROUPING SETS" do for you? Are you sure that's a query, and not a novel in some foreign language? mcg Douglas Knudsen <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/13/2007 03:28 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject Re: [ACFUG Discuss] CF, Oracle 9i, and GROUP BY GROUPING SETS darn lack of sleep. might have missed a paren or semi on the copy, but this is the gist. The GROUPING SETS is new to me. SELECT RS.REPNAME , U.EMPLID , TO_CHAR(U.DSMDATE,'HH24') AS GROUPHOUR , U.INTERVAL , ROUND(DECODE(SUM(DATA9)*RETURN_THRESHOLD(US.BUSUNIT,'DLRCPH',SYSDATE),0, 0,SUM(DATA12)/(SUM(DATA9)*RETURN_THRESHOLD(US.BUSUNIT,'DLRCPH',SYSDATE)) )*100,2 AS DLRATTAINPCT , ROUND(SUM(DATA12),2) AS DLRCALLS , ROUND(DECODE(NVL(SUM(DATA13),0),0,0,SUM(DATA19)/SUM(DATA13)),2) AS DLRCPH , ROUND(SUM(DATA11),2) AS EPYCOUNT , ROUND(SUM(DATA10),2) AS EPYDOLLARS FROM USER_SUP US LEFT JOIN REP_TO_SUP RS ON US.EMPLID = RS.SUPID LEFT JOIN UNIVERSAL_SUMMARY U ON RS.EMPLID = U.EMPLID WHERE US.BUSUNIT = '1234567890' AND US.EMPLID = '13579' AND TRUNC(U.DSMDATE) BETWEEN TO_DATE('02/13/2007','MM/DD/YYYY') AND TO_DATE('02/13/2007','MM/DD/YYYY') AND ( U.DSMDATE BETWEEN TO_DATE('02/13/2007 7:00 AM','MM/DD/YYYY HH:MI AM') AND TO_DATE('02/13/2007 5:00 PM','MM/DD/YYYY HH:MI AM') - 1/24 OR U.INTERVAL = 1 ) GROUP BY GROUPING SETS ( (RS.REPNAME, U.EMPLID, TO_CHAR(U.DSMDATE,'HH24'), U.INTERVAL, US.BUSUNIT), (RS.REPNAME, U.EMPLID, U.INTERVAL, US.BUSUNIT), (TO_CHAR(U.DSMDATE,'HH24'), U.INTERVAL, US.BUSUNIT), (U.INTERVAL, US.BUSUNIT) ) ORDER BY RS.REPNAME, GROUPHOUR On 2/13/07, Fennell, Mark P. < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > wrote: Can you share a sample of sql statement causing the error? Thanks. mf ________________________________ From: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] On Behalf Of Douglas Knudsen Sent: Tuesday, February 13, 2007 3:18 PM To: discussion@acfug.org <mailto:discussion@acfug.org> Subject: [ACFUG Discuss] CF, Oracle 9i, and GROUP BY GROUPING SETS I just updated our JDBC drivers to the latest 3.5 versions for testing out in a dev instance. We are planning to move to Oracle 10g next Q. A developer is seeing some errors with a query using GROUP BY GROUPING SETS. The error is [Macromedia][Oracle JDBC Driver][Oracle]ORA-03001: unimplemented feature Developer claims that he can use 3 or less GROUPING SETS, but as soon as he uses > 3 this error shows up. Anyone seen this before? -- Douglas Knudsen http://www.cubicleman.com <http://www.cubicleman.com/> this is my signature, like it? ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform <http://www.acfug.org?fa=login.edituserform/> For more info, see http://www.acfug.org/mailinglists <http://www.acfug.org/mailinglists> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ <http://www.mail-archive.com/discussion%40acfug.org/> List hosted by FusionLink <http://www.fusionlink.com/> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform <http://www.acfug.org?fa=login.edituserform/> For more info, see http://www.acfug.org/mailinglists <http://www.acfug.org/mailinglists> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ <http://www.mail-archive.com/discussion%40acfug.org/> List hosted by FusionLink <http://www.fusionlink.com/> ------------------------------------------------------------- -- Douglas Knudsen http://www.cubicleman.com <http://www.cubicleman.com/> this is my signature, like it? ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform <http://www.acfug.org?fa=login.edituserform/> For more info, see http://www.acfug.org/mailinglists <http://www.acfug.org/mailinglists> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ <http://www.mail-archive.com/discussion%40acfug.org/> List hosted by FusionLink <http://www.fusionlink.com/> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
