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: [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 http://www.fusionlink.com
-------------------------------------------------------------
