Home » SQL & PL/SQL » SQL & PL/SQL » Problems with procedures- SQL statement.
Problems with procedures- SQL statement. [message #36861] Fri, 28 December 2001 04:15 Go to next message
George Larry
Messages: 10
Registered: October 2001
Junior Member
I've got a procedure... that doesn't quite work... I'm getting a 'not
a GROUP BY expression ORA-06512' error. Could someone help me write
this so it works?
Here's what I've got... I'm trying to list the sums for each
ACCOUNTNUMBER, but order them by LNAME...
CREATE OR REPLACE PROCEDURE billByAcct(
rSet OUT reportC.rc,
uNum IN ASSIGNMENTS.USERID%TYPE,
rDep IN TELCOMUSERS.DEPTNAME%TYPE,
rCod IN TELCOMUSERS.CODE%TYPE,
aNum IN SERVICES.ACCOUNTNUMBER%TYPE,
fDat IN BILLING.INVOICEDATE%TYPE,
tDat IN BILLING.INVOICEDATE%TYPE,
stBa IN SERVICES.SERVICETYPE%TYPE,
stCe IN SERVICES.SERVICETYPE%TYPE,
stPa IN SERVICES.SERVICETYPE%TYPE,
stCa IN SERVICES.SERVICETYPE%TYPE,
stCi IN SERVICES.SERVICETYPE%TYPE,
seCa IN SERVICES.SERVICECATEGORY%TYPE
) AS
BEGIN
OPEN rSet FOR
SELECT
c.ACCOUNTNUMBER, a.FNAME, a.LNAME, b.JONNUM, c.CIRCUITNUMBER,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '10', d.BILLAMT,
0 ) ) AS BILLOCT,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '11', d.BILLAMT,
0 ) ) AS BILLNOV,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '12', d.BILLAMT,
0 ) ) AS BILLDEC,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '01', d.BILLAMT,
0 ) ) AS BILLJAN,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '02', d.BILLAMT,
0 ) ) AS BILLFEB,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '03', d.BILLAMT,
0 ) ) AS BILLMAR,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '04', d.BILLAMT,
0 ) ) AS BILLAPR,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '05', d.BILLAMT,
0 ) ) AS BILLMAY,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '06', d.BILLAMT,
0 ) ) AS BILLJUN,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '07', d.BILLAMT,
0 ) ) AS BILLJUL,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '08', d.BILLAMT,
0 ) ) AS BILLAUG,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '09', d.BILLAMT,
0 ) ) AS BILLSEP,
b.SERVSTART, b.SERVEND, c.DISCODATE
FROM
TELCOMUSERS a, ASSIGNMENTS b, SERVICES c, BILLING d
WHERE
a.USERID = b.USERID
AND
b.SERVICEID = c.SERVICEID
AND
b.ASSIGNREFNUM = d.ASSIGNID
AND
a.USERID = NVL( uNum, a.USERID )
AND
a.DEPTNAME = rDep
AND
a.CODE = rCod
AND
c.SERVICECATEGORY = seCa
AND
c.ACCOUNTNUMBER = NVL( aNum, c.ACCOUNTNUMBER )
AND
c.SERVICETYPE IN ( stBa, stCe, stPa, stCa, stCi )
AND
d.INVOICEDATE >= fDat
AND
d.INVOICEDATE <= tDat
GROUP BY
c.ACCOUNTNUMBER
ORDER BY
a.LNAME;
END;
/

----------------------------------------------------------------------
Re: Problems with procedures- SQL statement. [message #36875 is a reply to message #36861] Sat, 29 December 2001 18:40 Go to previous messageGo to next message
Bhavani
Messages: 5
Registered: December 2001
Junior Member
use this:

group by c.ACCOUNTNUMBER, a.FNAME, a.LNAME, b.JONNUM, c.CIRCUITNUMBER

instead of GROUP BY c.ACCOUNTNUMBER

Regards

Bhavani

----------------------------------------------------------------------
Re: Problems with procedures- SQL statement. [message #36886 is a reply to message #36861] Mon, 31 December 2001 07:31 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
your select stmt should be some what look like this

SELECT
h.ACCOUNTNUMBER, f.FNAME, f.LNAME, g.JONNUM, h.CIRCUITNUMBER,
g.SERVSTART, g.SERVEND, h.DISCODATE,e.billsep FROM
(SELECT
c.ACCOUNTNUMBER, 
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '10', d.BILLAMT, 
0 ) ) AS BILLOCT,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '11', d.BILLAMT, 
0 ) ) AS BILLNOV,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '12', d.BILLAMT, 
0 ) ) AS BILLDEC,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '01', d.BILLAMT, 
0 ) ) AS BILLJAN,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '02', d.BILLAMT, 
0 ) ) AS BILLFEB,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '03', d.BILLAMT, 
0 ) ) AS BILLMAR,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '04', d.BILLAMT, 
0 ) ) AS BILLAPR,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '05', d.BILLAMT, 
0 ) ) AS BILLMAY,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '06', d.BILLAMT, 
0 ) ) AS BILLJUN,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '07', d.BILLAMT, 
0 ) ) AS BILLJUL,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '08', d.BILLAMT, 
0 ) ) AS BILLAUG,
SUM( DECODE( TO_CHAR( d.INVOICEDATE, 'mm' ), '09', d.BILLAMT, 
0 ) ) AS BILLSEP

FROM
TELCOMUSERS a, ASSIGNMENTS b, SERVICES c, BILLING d
WHERE
a.USERID = b.USERID
AND
b.SERVICEID = c.SERVICEID
AND
b.ASSIGNREFNUM = d.ASSIGNID
AND 
a.USERID = NVL( uNum, a.USERID )
AND
a.DEPTNAME = rDep
AND
a.CODE = rCod
AND
c.SERVICECATEGORY = seCa
AND
c.ACCOUNTNUMBER = NVL( aNum, c.ACCOUNTNUMBER )
AND
c.SERVICETYPE IN ( stBa, stCe, stPa, stCa, stCi )
AND
d.INVOICEDATE >= fDat
AND
d.INVOICEDATE <= tDat
GROUP BY
c.ACCOUNTNUMBER
ORDER BY
a.LNAME) e ,TELCOMUSERS f, ASSIGNMENTS g, SERVICES h
where
e.accountnumber=h.accountnumber and
f.USERID = g.USERID
AND
g.SERVICEID = h.SERVICEID;



----------------------------------------------------------------------
Previous Topic: After Insert Trigger
Next Topic: UPPER returning doubles
Goto Forum:
  


Current Time: Fri Apr 19 15:25:56 CDT 2024