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 |
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 #36886 is a reply to message #36861] |
Mon, 31 December 2001 07:31 |
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;
----------------------------------------------------------------------
|
|
|
Goto Forum:
Current Time: Fri Nov 22 22:24:22 CST 2024
|