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] *On Behalf Of *Douglas
Knudsen
*Sent:* Tuesday, February 13, 2007 3:29 PM
*To:* discussion@acfug.org
*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]> wrote:
>
> Can you share a sample of sql statement causing the error? Thanks.
> mf
>
> ------------------------------
> *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *On Behalf Of *Douglas
> Knudsen
> *Sent:* Tuesday, February 13, 2007 3:18 PM
> *To:* 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
> 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 http://www.fusionlink.com
-------------------------------------------------------------
