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