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]> 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
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
--
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
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
