Ron,
Yes, there is a KEYWORD called ROLLUP which is used in Dimensions typically
found in a DW environment. I'm surprised that Oracle has not added this to
there list of "Oracle Reserved Words". Hopefully the following will help:
Ed Haskins
Oracle DBA
Verizon Wireless
ROLLUP
ROLLUP enables a SELECT statement to calculate multiple levels of subtotals
across a specified group of dimensions. It also calculates a grand total.
ROLLUP is a simple extension to the GROUP BY clause, so its syntax is
extremely easy to use. The ROLLUP extension is highly efficient, adding
minimal overhead to a query.
Syntax
ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is:
SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)
Details
ROLLUP's action is straightforward: it creates subtotals which roll up from
the most detailed level to a grand total, following a grouping list
specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list
of grouping columns. First, it calculates the standard aggregate values
specified in the GROUP BY clause. Then, it creates progressively
higher-level subtotals, moving from right to left through the list of
grouping columns. Finally, it creates a grand total.
ROLLUP creates subtotals at n+1 levels, where n is the number of grouping
columns. For instance, if a query specifies ROLLUP on grouping columns of
Time, Region, and Department (n=3), the result set will include rows at four
aggregation levels.
-----Original Message-----
Sent: Tuesday, April 24, 2001 4:47 PM
To: Multiple recipients of list ORACLE-L
Hi,
I just ran into a problem with v8.1.6 on Linux whereby my stored procedure
won't compile because it claims that a table reference is out of scope. It
complains about the definition of the arguments to the stored procedure
which are as follows:
CREATE OR REPLACE PROCEDURE rollup_insert
(
vv_rollup_id OUT rollup.rollup_id%TYPE,
vv_warrant_id IN rollup.warrant_id%TYPE,
vv_rollup_limit_id IN limit.limit_id%TYPE,
vv_rollup_time IN VARCHAR2,
vv_rollup_emp_badge IN rollup.rollup_emp_badge%TYPE,
vv_exec_id OUT INT,
vv_rc OUT INT
)
AS
...
The specific error message complains that "rollup" is out of scope. If I
substitute base level types for these, the procedure will compile. I have
included the "rollup" table definition and ownership below for reference.
It is the TWS user trying to execute this procedure.
desc rollup
----------------------------------------------------------------------------
-----------------
ROLLUP_ID NOT NULL NUMBER
WARRANT_ID NOT NULL NUMBER
ROLLUP_TIME NOT NULL DATE
ROLLUP_TIME_TZ NOT NULL VARCHAR2(3)
ROLLUP_EMP_BADGE NOT NULL VARCHAR2(4)
ROLLUP_LIMIT_ID NOT NULL NUMBER
DATESTAMP DATE
select owner, object_name, object_type from dba_objects where object_name
like '%ROLLUP%';
OWNER OBJECT_NAME OBJECT_TYPE
----------------------------------------------------------------------------
--------------------
TWS ROLLUP TABLE
TWS ROLLUP_INSERT PROCEDURE
As you can see, the table and the procedure are owned by the same user. The
table exists and the columns referenced in the arguments to the procedure
also exist.
What am I missing here? Is there a new keyword called ROLLUP somewhere?
Any help would be very much appreciated.
TIA
Ron Morton
Database Architect / Administrator
Union Switch & Signal Inc
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Morton, Ronald D
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Haskins, Ed
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
