One last thought. If it works in toad just create or replace myViewName as select... and then select from the view in your cfquery. :) mf
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Douglas Knudsen Sent: Tuesday, February 13, 2007 4:22 PM To: [email protected] Subject: Re: [ACFUG Discuss] CF, Oracle 9i, and GROUP BY GROUPING SETS if only you knew the meatball. :) passed the info along. thanks. We are able to execute this in Toad, SQL+, PL/SQL Developer, and SQL Developer. SQL Developer uses JDBC, so seems its related to teh 3.5 version of the data direct drivers. DK On 2/13/07, Fennell, Mark P. <[EMAIL PROTECTED]> wrote: Dang... now that's some serious sql spaghetti. While I don't see anything in the documentation for 9.2 regarding limits on grouping sets, it might not hurt to consider using the cube or rollup group statement keywords, creating a view to simplify the decodes and rounds and other functions, and, this might actually help, put the joins in the where clause. Apparently, oracle doesn't do joins in the from clause quite up to SQL standards and in fact will mess up results. It's a "feature." hth. mf ________________________________ From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] On Behalf Of Douglas Knudsen Sent: Tuesday, February 13, 2007 3:29 PM To: [email protected] 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]> ] On Behalf Of Douglas Knudsen Sent: Tuesday, February 13, 2007 3:18 PM To: [email protected] 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 this is my signature, like it? ------------------------------------------------------------- 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 FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- -- Douglas Knudsen 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 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 FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- -- Douglas Knudsen 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 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 -------------------------------------------------------------
