Combining refCursors and Summing

Posted on 16th Feb 2014 07:03 pm by admin

Given the following DDL:

CREATE TABLE FOODSALESTEST ("WEEKNBR" NUMBER, "STOREID" NUMBER NOT NULL, "GUESTS" NUMBER NOT NULL, "TACOSALES" NUMBER NOT NULL, "SOUPYSALES" NUMBER NOT NULL); Insert into FoodSalesTest VALUES (1,74,302,3021.71,3021.71); Insert into FoodSalesTest VALUES (1,83,231,2106.58,2106.58); Insert into FoodSalesTest VALUES (1,101,251,2163.5,2163.5); Insert into FoodSalesTest VALUES (1,113,298,2953.82,2953.82); Insert into FoodSalesTest VALUES (2,74,131,1168.11,1168.11); Insert into FoodSalesTest VALUES (2,83,156,1258.22,1258.22); Insert into FoodSalesTest VALUES (2,101,164,1550.87,1550.87); Insert into FoodSalesTest VALUES (2,113,129,1384.26,1384.26); Insert into FoodSalesTest VALUES (3,74,144,1594.02,1594.02); Insert into FoodSalesTest VALUES (3,83,196,1867.2,1867.2); Insert into FoodSalesTest VALUES (3,101,196,2052.96,2052.96); Insert into FoodSalesTest VALUES (3,113,142,1490.63,1490.63); Insert into FoodSalesTest VALUES (7,74,192,2496.17,2496.17); Insert into FoodSalesTest VALUES (7,83,178,1971.54,1971.54); Insert into FoodSalesTest VALUES (7,101,294,3477.88,3477.88); Insert into FoodSalesTest VALUES (7,103,336,3398.42,3398.42); --=== CREATE OR REPLACE PROCEDURE GETWEEKLYFOODSALES (inweeknbr number , p_rcWeekly in out sys_refcursor ) is BEGIN OPEN p_rcWeekly FOR -- This has been simplified and as is not very practical. -- It is actually a huge select statement with 6 "WITH" clauses and 15 joins and many selection criteria parameters Select * from FoodSalesTest where weeknbr = inWeekNbr; END GetWeeklyFoodSales; --=== CREATE OR REPLACE PROCEDURE GETALLFOODSALES( NbrOfWeeks In number , p_rcWeek1 Out sys_refcursor , p_rcWeek2 out sys_refcursor , p_rcWeek3 out sys_refcursor , p_rcWeek7 out sys_refcursor ) is begin GetWeeklyFoodSales( 1 , p_rcWeek1); GetWeeklyFoodSales( 2 , p_rcWeek2); if NbrOfWeeks = 3 then GetWeeklyFoodSales( 3 , p_rcWeek3); end if; GetWeeklyFoodSales( 7 , p_rcWeek7); END GetAllFoodSales;
GetWeeklyfoodSales is actually a huge select statement with 6 "WITH" clauses and 15 joins and many selection criteria parameters. It is probably possible to combine all functionality in 1 stored procedure but I want to modularize.

1) How do I select all cursors into 1 single cursor in GetAllFoodSales?

2) Why Cant I do this? = why isn't the cursordata maintained since it is defined as IN OUT in GetWeeklyFoodSales?
GetWeeklyFoodSales( 1 , p_TheOnlyCursor);
GetWeeklyFoodSales( 2 , p_TheOnlyCursor);
if NbrOfWeeks = 3 then
GetWeeklyFoodSales( 3 , p_TheOnlyCursor);
end if;
GetWeeklyFoodSales( 7 , p_TheOnlyCursor);

WHERE invoked procedure has these parms:
inweeknbr IN number
, p_rcWeekly IN OUT sys_refcursor

3) I would also like to have 2 more output cursor in GetAllFoodSales. Descriptively:
1. p_SummedResultsByStoreID
2. p_SummedResultsByWeekNumber

These must be separate cursors in order for program to consume for report

Your Answer:

Login to answer

Other forums

Calculus Help (and by help I mean homework)
Hrmmm, I hate posting about math homework, partly because it's homework, and partly because I hate w

PL/SQL: ORA-00947: not enough values error message
Hi all i am getting Error(25,63): PL/SQL: ORA-00947: not enough values error message when executing

Sending UDP Raw socket
I truly need help on this. I am trying to write a test program that simulate a network environment

Session is not saving
I am not trying to do anything too fancy, I am just trying to get some $_SESSION data to save and us

cstdatomic (c++0x std::atomic) / g++ 4.4
Hello,

I'm trying to use cstdatomic (std::atomic in the upcoming c++0x standard) in g++

php or sql?
Sorry not sure if this is a sql problem or php the following code is supposed to delete data from th

help with calculations on a flat text file
hello,
I have this code below that is attached to a flat file like this:
Email:LastName:FirstN

Inserting into MySQL Newbie
Hi ive got a slight problem where ive made a simple web form where the customer inserts the ammount

can i optmize image to 50% quality before uploading 2 server
Hi there

i have jus written a php program to upload and display images but when i try to uplo

Javascript using window.location seems to lose state
Not sure what forum so let me know if I'm in the wrong place. I have a main window with a ajax grid